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