Column total incorrect for converted amounts

[SUNNY_ISLAND]

I have the following dax formula to convert values from various currencies to SGD. The total column does not add up. Instead of showing $16.5m, it is showing $25.2m. The converted amount on each row is correct but not the total for the column. 

Appreciate advice on how to get the DAX

-----

Total value in SGD =

VAR

    _CURRENCY = MAX(Data[Report curr])

VAR

    _CONVERSIONRATE =

        calculate(max('Exch rate'[Exch rate]), 'Exch rate'[Report Curr] = _CURRENCY)

RETURN

    sumx(data,_CONVERSIONRATE*(Data[Value]))

----

[PowerUserR · Accepted solution]

Hi,

I think this topic is in regards to a similar problem: https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/td-p/547907

[v-rongtiep-msft · Accepted solution]

Hi @SUNNY_ISLAND ,

Please have a try.

Based on the measure[Total value in SGD].

_contract_ = 
var _b = SUMMARIZE('Date','Date'[Report curr],"aaa",'Data'[Total value in SGD])
return
IF(HASONEVALUE('Date'[Report curr]),'Data'[Total value in SGD],SUMX(_b,[aaa]))

Or you can use Other unique columns replace the column 'date'[Report curr].

If I have misunderstood your meaning, please provide a pbix file without privacy information and more details with your desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PowerUserR · Accepted solution]

Hi,

I think this topic is in regards to a similar problem: https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/td-p/547907

[Fowmy]

@SUNNY_ISLAND 
Please try the following version:

Total value in SGD =
VAR _CURRENCY =
    MAX ( Data[Report curr] )
RETURN
    SUMX (
        data,
        CALCULATE (
            MAX ( 'Exch rate'[Exch rate] ),
            'Exch rate'[Report Curr] = _CURRENCY
        ) * ( Data[Value] )
    )

[SUNNY_ISLAND]

Thanks for your suggestion but it is still not working. The end results remain the same. Would appreciate alternative suggestions. Much thanks