Incorrect total in Matrix Total

[HanishR]

I have a Matrix with a currency conversion in place for the Values, 
The currency conversion rate is picked up by the following formula: 

TRNRate = MIN('Currency Table'[Exchange Rate])
Where my exchange rate table has ONE value for every month and currency combination. 
Column headers in Exchange Rate Table:
Date | Report Currency | Transaction Currency | Exchange Rate
e.g. 1st of month | EUR | GBP | 0.8578
I have the relationships for dates and Sales Currency linked to Transaction Currency
My Conversion rate is calculated with the following formula: 

Sales Converted =IF(HASONEVALUE('Report Currency'[Report Currency]),SUMX(Sales,Sales[Sales])/[TRNRate])

As you can see the Converted row for UK, is not in fact totaling correctly, 

It is taking the TOTAL SALES for all months in GBP Currency and performing the conversion currency on the Min rate of all months, as opposed to individual month basis. 

Please let me know if you need any more info, and thanks in advance for the help. 

Cheers

Hanish

[v-yiruan-msft · Accepted solution]

Hi @HanishR ，

Please try to update your measure with the below formula and applied this new created measure on the visual to replace measure Sales Converted:

Measure=SUMX(VALUES('TABLE'[Category]),SUMX(VALUES('TABLE'[COUNTRY]),[Sales Converted]))

Or you can refer the contents in the following links to update the formula of your measure and get the correct results:

Totals incorrect

Why Your Total Is Incorrect In Power BI

If the above methods can't help you, you can provide some sample data which applied on the visual and existed relationship if the data from multiple tables.

Best Regards

Rena

[amitchandak]

@HanishR , Try to push the row context

Sales Converted new =
sumx(summarize(Table, Table[category],Table[Country],"_1",[Sales Converted]),[_1])