Forum Discussion
Subtotal not correct
I'm using a table visualization in Power BI and I'm finding the subtotal for both of these are not correct:
a) With Sum:
AmountQTD = SUM([AmountSignAdj])- if(max(dimPeriodDAX[Month])>3,CALCULATE(SUM([AmountYTD]),PREVIOUSQUARTER(dimPeriodDAX[Date])),0)
b) With SumX
AmountQTD = SUM([AmountYTD])- if(max(dimPeriodDAX[Month])>3,CALCULATE(SUMX('Data',[AmountYTD]),PREVIOUSQUARTER(dimPeriodDAX[Date])),0)
Any suggestions?
Here is a link to a demo file:
https://www.dropbox.com/s/u9z0i3qlkujxdxz/QTDMockup.pbix?dl=0
My ideal desired output is this, or as close to this as possible:
Attached is my reply, although i dont' understand why you are totaling the 'AmountYTD' values in 2016 to get 1000. Seems like you would want the subtotal to be 400 for 2016.
This gives me this strange result (AmountYTD is the original dataset, testQTD is the new result:
I'll continue thinking throught it myself too.
UPDATE:
This is almost there:
testQTD =
var _thisQ = TOTALQTD( [Total Amount], dimDates[DAXDate] )
var _lastQ = TOTALQTD( [Total Amount] , PREVIOUSQUARTER( dimDates[DAXDate] ), VALUES( dimDates[Year] ) )
RETURN
IF(_thisQ = 0,
BLANK(),
_thisQ - _lastQ)Just got to figure out how to total 2017.
8 Replies
Attached is my reply, although i dont' understand why you are totaling the 'AmountYTD' values in 2016 to get 1000. Seems like you would want the subtotal to be 400 for 2016.
Thanks - this is good though I don't fully understand it yet (will have to study it).
The YTD was just me repeating the original problem so that we can see a side by side comparison of problem and solution.
Thanks for your help, really appreciate the speedy response by the community!
For everyone;'s benefit the formula was:
testQTD = var _thisQ = TOTALQTD( [Total Amount], dimDates[DAXDate] )
var _lastQ = TOTALQTD( [Total Amount] , PREVIOUSQUARTER( dimDates[DAXDate] ), VALUES( dimDates[Year] ) )
RETURN
_thisQ - _lastQActually is there a way of stopping the formula creating a negative entry in the very last + 1 quarter? So "If this quater amount = 0 then do nothing" ?
Hi,
Share some data and show the expected result.
Just editted the original post to include a sample model
Hi PuzzledSpark
I would suggest reading through this great blog post from PowerPivotPro, where they explain why the totals do not add up, due to the way the filtering is applied on tables.
And then shows how to ensure that you can get the totals you want to be displayed correctly when using a table.
https://powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/
