Total Not Adding Up - Price Volume Matrix Waterfall

[edtm]

Hello.

I am attempting to build a price volume matrix waterfall.  In the image below, I did all the calculations in the first table.  And, I created a check table in the table below it.  The "Combined" amounts add up correctly but the Mix/Price/Volume elements are not.  How could I fix this? 

Sample file below:

Waterfall

Thanks!  Ed

[WinterMist · Accepted solution]

@edtm & @gigantor28 

Thanks for the access!

So the key learning item here is that Power BI does not calculate the Total row as a SUM of the row values.

(This has been annoying for me as well.)

Instead, it performs the calculation in the Total Row filter context, completely ignoring all other rows.

This often results in a Total Amount which is NOT the SUM of the row values.

Anyway, to get around this, we just need to:

1) Put the base calculation in its own measure.

2) Create another measure which does a SUMX over the first measure, thereby creating Context Transition.

NOTE: SUMX essentially iterates over the values (in this case we want to iterate over Device Type), and simply sum the values.

Step 1) Create measures with just the base calculations.

Step 2) Now call each of those measures,

- performing the regular measure IF Device Type is filtered (i.e. if it's NOT the total row)

- OR performing the override SUM OF TOTAL ROW VALUES if Device Type is NOT filtered (i.e. it IS the total row.)

RESULT: As you can see, the total row now shows the SUM of row values for each measure.

Hope this is helpful to you!

Nathan

[edtm]

Thank you!  I was actually able to solve the issue after watching the video you linked in your other reply.  But, thank you for adding additional context into this reply. 

[giri41]

what happens to the above result if you change dimensions instead of device type ,

[WinterMist]

@edtm 

Btw, just requested permission to the link you shared.  It said I did not have permission.

Thanks,

Nathan

[gigantor28]

permission granted

[WinterMist]

@edtm 

My apologies, after using Excel to SUM, I realized the problem is simply the classic total does not equal the sum of row values Power BI behavior.  

Please watch this video.   It provides the solution.

Regards,

Nathan

https://www.youtube.com/watch?v=wHAWZzVS48Q

[WinterMist]

@edtm 

1) Would it be possible for you to upload the PBIX to Google Drive, and share the link here?  (I no longer have a DropBox account.)

2) If it is not possible to share the PROD PBIX, can you share:

- a small TEST data set PBIX that is still able to reproduce the mismatch problem

- The measures for at least 2 of the mismatched pairings.

   - Analysis Mix vs. Check Mix

   - Analysis Price vs. Check Price

   - Analysis Volume vs. Check Volume

Problems like this typically have to do with Filter Context.

Regards,

Nathan

[edtm]

@WinterMist 

Here you go:

Waterfall