Forum Discussion
Compute difference between Subtotals
Hi ,
Can some one guide me on how to calculate the difference between two subtotals in DAX?
Right, again, no sample data so can't be specific. Hint.
But, in general, you want to do this. Take a look at your visual and see how it is aggregated. Let's say it is by "Thing". You want to create a virtual table using a VAR that emulates this and your columns. Then use SUMX and basic math. Something like this:
Subtotal Difference = VAR __Table = SUMMARIZE( 'Table', [Thing], "2019",SUM([2019), "2020",SUM([2020) ) RETURN SUMX(__Table,[2019]) - SUMX(__Table,[2020])
4 Replies
The information you have provided is not making the problem clear to me. Can you please explain with an example.
In case you need diff between two years, you can use time intelligence. With Date table.
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31")) This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31")) Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31")) Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31")) Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Are those measures or column aggregations? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
This *might* help if it is measures. This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907These are column aggregations. I want to calculate the differenence between these 2 subtotals. How can i add a measure to calculate this?
Right, again, no sample data so can't be specific. Hint.
But, in general, you want to do this. Take a look at your visual and see how it is aggregated. Let's say it is by "Thing". You want to create a virtual table using a VAR that emulates this and your columns. Then use SUMX and basic math. Something like this:
Subtotal Difference = VAR __Table = SUMMARIZE( 'Table', [Thing], "2019",SUM([2019), "2020",SUM([2020) ) RETURN SUMX(__Table,[2019]) - SUMX(__Table,[2020])
