Correct Row Totals but Incorrect Row Totals on Table Matrix

[VincentJT]

Hi Power BI experts,

I was hoping you could help me out with an issue that I’ve been having with incorrect row totals on the table matrix.

Background
I am trying to calculate the month on month FX impact on profit margins by different currency pairs

Total FX Impact = Revenue FX Impact - Cost FX Impact
Revenue FX Impact = USD Revenue - (Local Currency Revenue / Revenue Exchange Rate Last Month), which I have calculated using a measure with the below DAX formula

Cost FX Impact = USD Cost - (Local Currency Cost / Cost Revenue Exchange Rate Last Month),
which I have calculated using a measure with the below DAX formula

Total FX Impact = Revenue FX Impact - Cost FX Impact , which I have also calculated using a measure with the below DAX formula

When in a table matrix, I am getting the correct row totals, but not the column totals. The column totals are wrong because it uses the total amounts to do the measure calculations, whereas the totals should just be a summation of the FX impact by individual currencies. A screenshot of the wrong column totals is below.

I have read through and seen quite a few videos and understand that it has to do with the context of the table, but I’m at my wit’s end as to how to resolve this after hours of trial and error. I have also tried to summarize by [Currency Pair] , but the FX impact calculation would then be wrong.

I would really appreciate it if I could have some guidance as to where I am going wrong.

Thank you!

[BrianJ]

@VincentJT,

Welcome to the forum! – great to have you here.

I actually just worked through a nearly identical situation with a member last week. In a matrix, you have four separate evaluation contexts you have to deal with individually. I find that rather than using the SUMMARIZE/SUMX construct that works so well for correcting totals in a table with only two different evaluation contexts, the SWITCH(TRUE)/HASONEVALUE construct in the solution below works better to control for the four separate contexts.

Sales forecasting with Ramp Up Formula Part 2

@msedlak, Okay, think I’ve got a solution for you on this one as well. Let’s start with the totals first, since that ended up being the harder part - once we get that, you’ll see the stages calculation just becomes a drag-and-drop. The challenging aspect of correcting totals in a matrix is that you have four different evaluation contexts to deal with and correct. The way I handled this was with a SWITCH(TRUE() ) construct to manage the four different combinations of HASONEVALUE = TRUE/FALSE …

The solution above also contains a PBIX file that will allow you to deconstruct the details of the solution. If you still have questions or problems after going through that, just give a shout and I’ll be happy to work through the issues with you.

I hope this is helpful.

• Brian

[Greg]

Hi @VincentJT. Another thread that might be of use is the Fix Incorrect Totals post in the DAX Patterns category. Hope this helps. Greg

Fix Incorrect Totals

When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue. When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX…

[EnterpriseDNA]

Hi @VincentJT, did the response provided by the contributors help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

[VincentJT]

Hi Brian,

Thanks alot for this!

This totally solved the issue that I was having. A life savior indeed!

Vincent

[BrianJ]

@VincentJT,

Great – glad to hear that worked well for you.

– Brian