Forum Discussion

SUNNY_ISLAND's avatar
SUNNY_ISLAND
Frequent Visitor
4 years ago
Solved

Column total incorrect for converted amounts

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]))
----
 

 

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

4 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      SUNNY_ISLAND
      Frequent Visitor
      4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.