Forum Discussion

HanishR's avatar
HanishR
Frequent Visitor
6 years ago
Solved

Incorrect total in Matrix Total

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
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    HanishR , Try to push the row context

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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