Correct Row Totals but Incorrect Row Totals on Table Matrix

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

image

image

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

image

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.

image

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!

@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.

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

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

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!

Hi Brian,

Thanks alot for this!

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

Vincent

@VincentJT,

Great – glad to hear that worked well for you.

– Brian