topic Total Not Adding Up - Price Volume Matrix Waterfall in DAX Commands and Tips

Total Not Adding Up - Price Volume Matrix Waterfall

edtm — Thu, 08 Feb 2024 01:03:24 GMT

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

Re: Total Not Adding Up - Price Volume Matrix Waterfall

WinterMist — Thu, 08 Feb 2024 15:26:28 GMT

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

Re: Total Not Adding Up - Price Volume Matrix Waterfall

edtm — Thu, 08 Feb 2024 16:34:19 GMT

WinterMist

Here you go:

Waterfall

Re: Total Not Adding Up - Price Volume Matrix Waterfall

WinterMist — Thu, 08 Feb 2024 16:42:39 GMT

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

Re: Total Not Adding Up - Price Volume Matrix Waterfall

WinterMist — Thu, 08 Feb 2024 16:50:15 GMT

edtm

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

Thanks,

Nathan

Re: Total Not Adding Up - Price Volume Matrix Waterfall

gigantor28 — Thu, 08 Feb 2024 17:39:16 GMT

permission granted

Re: Total Not Adding Up - Price Volume Matrix Waterfall

WinterMist — Thu, 08 Feb 2024 20:41:58 GMT

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

Re: Total Not Adding Up - Price Volume Matrix Waterfall

edtm — Thu, 08 Feb 2024 20:55:52 GMT

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.

Re: Total Not Adding Up - Price Volume Matrix Waterfall

Anonymous — Fri, 22 Mar 2024 09:36:18 GMT

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