Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PuzzledSpark
Frequent Visitor

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:

DesiredOutput.png

2 ACCEPTED SOLUTIONS
mattbrice
Solution Sage
Solution Sage

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

 

 

View solution in original post

This gives me this strange result (AmountYTD is the original dataset, testQTD is the new result:

Untitled.png

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)

 

Untitled.png

 

Just got to figure out how to total 2017.

View solution in original post

8 REPLIES 8
mattbrice
Solution Sage
Solution Sage

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

 

 

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

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

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
   )




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

This gives me this strange result (AmountYTD is the original dataset, testQTD is the new result:

Untitled.png

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)

 

Untitled.png

 

Just got to figure out how to total 2017.

GilbertQ
Super User
Super User

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Ashish_Mathur
Super User
Super User

Hi,

 

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Just editted the original post to include a sample model

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.