Total Not Adding Up - Price Volume Matrix Waterfall

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