Subtotal not correct

[PuzzledSpark]

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:

[mattbrice · Accepted solution]

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.  

Sample reply

[mattbrice · Accepted solution]

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.  

Sample reply

[PuzzledSpark]

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 - _lastQ

[PuzzledSpark]

Actually 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" ?

[GilbertQ]

Hi @PuzzledSpark

You could modify your measure with the following:

testQTD = var _thisQ = TOTALQTD( [Total Amount], dimDates[DAXDate] )
var _lastQ =  TOTALQTD( [Total Amount] , PREVIOUSQUARTER( dimDates[DAXDate] ), VALUES( dimDates[Year] ) )
RETURN
IF(_lastQ = 0, 
        BLANK(),
        _thisQ - _lastQ
   )

[PuzzledSpark · Accepted solution]

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.

[GilbertQ]

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/

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[PuzzledSpark]

Just editted the original post to include a sample model