***** Learning Power BI? ***** FREE COURSE - Ultimate Beginners Guide To Power BI - portal.enterprisedna.co/p/ultimate-beginners-guide-to-power-bi FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterprisedna.co/p/ultimate-beginners-guide-to-dax FREE - 60 Page DAX Reference Guide Download - enterprisedna.co/dax-formula-reference-guide-download FREE - Power BI Resources - enterprisedna.co/power-bi-resources Enterprise DNA Membership - enterprisedna.co/membership Enterprise DNA Online - portal.enterprisedna.co/ Enterprise DNA Events - enterprisedna.co/enterprise-dna-events-page/ ***** Related Links ***** Calculate Rolling Totals Using DATESBETWEEN In Power BI - blog.enterprisedna.co/calculate-rolling-totals-in-power-bi-using-dax/ Calculating % of Totals - blog.enterprisedna.co/calculating-of-totals/ Dynamically Compare Current Totals To Last Years Totals - blog.enterprisedna.co/dynamically-compare-current-totals-to-last-years-totals/ ***** Related Course Modules ***** Ultimate Beginners Guide to DAX - portal.enterprisedna.co/p/ultimate-beginners-guide-to-dax Financial Reporting w/Power BI - portal.enterprisedna.co/p/financial-reporting-power-bi DAX Formula Deep Dives - portal.enterprisedna.co/p/dax-formula-deep-dives ***** Related Support Forum Posts ***** Cumulative Total Prior Year Matrix Total - forum.enterprisedna.co/t/cumulative-total-prior-year-matrix-total/2582 Visual Total Not The Grand Total I’m Expecting - forum.enterprisedna.co/t/visual-total-not-the-grand-total-im-expecting/2232 Last Reported Total And Totalling In Matrix - forum.enterprisedna.co/t/last-reported-total-and-totalling-in-matrix/875 For more advanced DAX support queries to review see here…. - forum.enterprisedna.co/search?q=advanced%20DAX
Cheers for that video! Short, simple, with great explanations and you prove that summarize formula isn't that bad as it seems to be. By the way - Merry Christmas!
Great Video. Learned a lot about the logic of these kinds of formulas. A'm attempting to apply this rationale to a YTD formula but unsuccessful. A tutorial on that would be most appreciated!!!
Hi Ezequiel, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!
My view on this is that the difference is really quite small on the majority of datasets Power BI users work on, so I just simplify it. Maybe a very large datasets you can see a notable difference but personally I never have. I'm bigger on simplification and it works for me.
Hello snapper kid, thank You for posting your query onto our channel. Well we're very much focused onto the Power BI related contents onto our channel so haven't tried the formula into the Power Pivot option. We're providing few of the links of the videos created and available onto our channel pertaining to the topics about "How to Fix The Incorrect Totals In Power BI". For furthermore queries, you can reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link our forum provided for the reference as well. Hoping you find this useful and meets your requirements that you've been looking for. Happy Learning!!! forum.enterprisedna.co/t/fix-incorrect-totals/8834 forum.enterprisedna.co/
I particularly appreciated that you narrated the formula in such a way that it is clear what you wanted to do, and indeed, what it entails. But I was less successful to apply your lesson to my example: summing per month, per year and overall total,. it sounds like it sum up the expected subtotal as many time as there are months...and the same way for the grand total. So 3 months shows 3 time the actual year subtotal, and 2 years shows twice the actual grand total. That makes me crazy ;-) I have the feeling you make twice the computation : once in the virtual table, then again in the expression of the Iterative SUMX (..., [average.]), nop? MthInvoiceBis:=VAR MthInvoice=SUMX(VALUES('Calendar'[Date]);1) VAR ListOfInvoices=SUMMARIZE('Calendar';'Calendar'[Year];'Calendar'[Month];"Invoice";MthInvoice) RETURN IF(HASONEFILTER('Calendar'[EngMonth]);MthInvoice; IF(HASONEFILTER('Calendar'[Year]); SUMX(ListOfInvoices;[Invoice]); SUMX(ListOfInvoices;[Invoice]) )) It gives this table . MthInvoiceBis 2019 € 276 => instead of 92. Actually 276 is the totol 92 x 3 months ! Oct € 31 Nov € 30 Dec € 31 2020 € 120 => instead of 60. Same as above 120 is 60 x2 months Jan € 31 Feb € 29 Grand Total € 760 It's weird, isn't it?
***** Learning Power BI? *****
FREE COURSE - Ultimate Beginners Guide To Power BI - portal.enterprisedna.co/p/ultimate-beginners-guide-to-power-bi
FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterprisedna.co/p/ultimate-beginners-guide-to-dax
FREE - 60 Page DAX Reference Guide Download - enterprisedna.co/dax-formula-reference-guide-download
FREE - Power BI Resources - enterprisedna.co/power-bi-resources
Enterprise DNA Membership - enterprisedna.co/membership
Enterprise DNA Online - portal.enterprisedna.co/
Enterprise DNA Events - enterprisedna.co/enterprise-dna-events-page/
***** Related Links *****
Calculate Rolling Totals Using DATESBETWEEN In Power BI - blog.enterprisedna.co/calculate-rolling-totals-in-power-bi-using-dax/
Calculating % of Totals - blog.enterprisedna.co/calculating-of-totals/
Dynamically Compare Current Totals To Last Years Totals - blog.enterprisedna.co/dynamically-compare-current-totals-to-last-years-totals/
***** Related Course Modules *****
Ultimate Beginners Guide to DAX - portal.enterprisedna.co/p/ultimate-beginners-guide-to-dax
Financial Reporting w/Power BI - portal.enterprisedna.co/p/financial-reporting-power-bi
DAX Formula Deep Dives - portal.enterprisedna.co/p/dax-formula-deep-dives
***** Related Support Forum Posts *****
Cumulative Total Prior Year Matrix Total - forum.enterprisedna.co/t/cumulative-total-prior-year-matrix-total/2582
Visual Total Not The Grand Total I’m Expecting - forum.enterprisedna.co/t/visual-total-not-the-grand-total-im-expecting/2232
Last Reported Total And Totalling In Matrix - forum.enterprisedna.co/t/last-reported-total-and-totalling-in-matrix/875
For more advanced DAX support queries to review see here…. - forum.enterprisedna.co/search?q=advanced%20DAX
I would upvote this a hundred times if I could. This came to my rescue today. Thank you so much!
Cheers for that video! Short, simple, with great explanations and you prove that summarize formula isn't that bad as it seems to be.
By the way - Merry Christmas!
Great Video. Learned a lot about the logic of these kinds of formulas. A'm attempting to apply this rationale to a YTD formula but unsuccessful. A tutorial on that would be most appreciated!!!
This was brilliant thank you so much worked perfectly!
Thank you very much. I believe I will use it soon.
Yes! THANK YOU
Hi Ezequiel, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!
Thank you so much! Very very helpful!
Great stuff Sam. SQLBI guys are pretty heavy on not using SUMMARIZE and using ADDCOLUMNS instead, thoughts?
My view on this is that the difference is really quite small on the majority of datasets Power BI users work on, so I just simplify it. Maybe a very large datasets you can see a notable difference but personally I never have. I'm bigger on simplification and it works for me.
That was very interesting and useful tip, will wait for more tips like this
Good video.. Solved a lot of my issues
Hi Sam, really a good technique to solve this kind of problems. Thanks and Thumbs Up !!!
Does the corrected total measure work in power pivot?
Hello snapper kid,
thank You for posting your query onto our channel.
Well we're very much focused onto the Power BI related contents onto our channel so haven't tried the formula into the Power Pivot option.
We're providing few of the links of the videos created and available onto our channel pertaining to the topics about "How to Fix The Incorrect Totals In Power BI".
For furthermore queries, you can reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link our forum provided for the reference as well.
Hoping you find this useful and meets your requirements that you've been looking for.
Happy Learning!!!
forum.enterprisedna.co/t/fix-incorrect-totals/8834
forum.enterprisedna.co/
No doubt, very powerful stuff! Many thanks for sharing!
Thanks Man!!! Important Stuff
This is great, but I can't make it work with calendar year last year's sum... still trying to find solutions.
I don't have enough words to thank you
I particularly appreciated that you narrated the formula in such a way that it is clear what you wanted to do, and indeed, what it entails.
But I was less successful to apply your lesson to my example: summing per month, per year and overall total,. it sounds like it sum up the expected subtotal as many time as there are months...and the same way for the grand total. So 3 months shows 3 time the actual year subtotal, and 2 years shows twice the actual grand total. That makes me crazy ;-)
I have the feeling you make twice the computation : once in the virtual table, then again in the expression of the Iterative SUMX (..., [average.]), nop?
MthInvoiceBis:=VAR
MthInvoice=SUMX(VALUES('Calendar'[Date]);1)
VAR ListOfInvoices=SUMMARIZE('Calendar';'Calendar'[Year];'Calendar'[Month];"Invoice";MthInvoice)
RETURN
IF(HASONEFILTER('Calendar'[EngMonth]);MthInvoice;
IF(HASONEFILTER('Calendar'[Year]);
SUMX(ListOfInvoices;[Invoice]);
SUMX(ListOfInvoices;[Invoice])
))
It gives this table
. MthInvoiceBis
2019 € 276
=> instead of 92. Actually 276 is the totol 92 x 3 months !
Oct € 31
Nov € 30
Dec € 31
2020 € 120
=> instead of 60. Same as above 120 is 60 x2 months
Jan € 31
Feb € 29
Grand Total € 760
It's weird, isn't it?
Thank you
Although I get the Total correct, but when I drill down, the Branch wise datas are not reflecting. In my database.
Straight up boss mode on that formula
thank you , work correctly except if the column has (ZERO)
ex : if there is no sales on February , how can i solve this
Thanks Vidler, for help like this check out the Enterprise DNA Support Forum - forum.enterprisedna.co/search?q=Totals%20Zero
How do I limit Average to only last 3 years using AVERAGEX and VALUES ? Jan 2019, 2018 and 2019.
neat job
great trick.
My hasfilter is showing true even for totals.. how can i fix it please help
Hi Aditya, check out our forum site for some ideas on this - forum.enterprisedna.co/search?q=Calculate%20Totals