DAX Pattern: Currency Conversion

DAX DAX Calculations
1

Hi our Experts again,

I am facing a weird issue while trying to create currency conversion measures in my current work. I have an existing column that gives me the right values and aggregations. But, I am willing to create it in measure to reduce the file size. The DAX pattern I used gives me the right row values but my aggregations are not coming right. The data model and relationships are simple but I am not understanding the problem. (I watched some of our previous content, but this problem seems more simple, though I can’t get the issue)

Below is a screenshot of the problem.

  1. The green marked column is correct and I want to produce the same result in measure.
    image
    image1063Ă—580 187 KB

I am attaching the PBIX along with the required data in it.
Would be nice to get your suggestion and help with it.

Remarks: I don’t want to create any new calculated column as my objective is to reducing columns.
currency work_Demo.pbix (7.2 MB)

1 Like
2

@sheik_mishuk
Thanks for posting your query.
I think you forgot to attach the pbix file.

3

Hi Sedhosen, sorry for missing it previously. I just have uploaded it.

4

First of all, since I am a big fan of the Measure Branching technique, I created a measure to show the Total Value Amount using the following simple formula:

Total Value Amount = SUM(Cobra[ValueAmount])

It is not mandatory for solving this problem, but it definitely helps you to have a clear DAX code and understand it better. So, I highly recommend you to use it from now on.

What I found was that you want to aggregate a column named “ValueAmount”, but the aggregation formula is different when the currency is equal or not equal to “USD”. So, I used Variables to break down the formula for these two situations.

  • The first part (VAR USDValueAmount) is for the time currency=USD in which you just need to use the Total Value Amount measure.

  • The second part (VAR NonUSDValueAmount) is for the time currency<>USD in which you need to multiply the ValueAmount by the related SalesRate in the ToUSD table. In these situations, you need to use the RELATED function, and since you need to iterate this multiplication function for each row in your fact table, you also need to use an iterating function like SUMX over SUM.

At the end, the Final Result would be the summation of what you achieved in the two above steps. You can see all I did in the following measure. I changed the 1.USDFixed21 measure as the following:

1.USDFixed21 Hossein = 
VAR USDValueAmount =
    CALCULATE ( [Total Value Amount], FILTER ( Cobra, Cobra[Currency] = "USD" ) )
VAR NonUSDValueAmount =
    CALCULATE (
        SUMX ( Cobra, Cobra[ValueAmount] * RELATED ( TO_USD[SalesRt] ) ),
        FILTER ( Cobra, Cobra[Currency] <> "USD" )
    )
RETURN
    USDValueAmount + NonUSDValueAmount

currency work_Demo.pbix (7.2 MB)

Hope it helps you,
Best,
Hossein

1 Like
5

Hi Sedhosen, you are amazing and spot on.
I understood the pattern you used through Sumx and I had been getting the wrong aggregation as I missed iterating through every row.

Thanks again for your quick help. :pray:

Regards

1 Like
6

Happy to hear that it helped you :blush:
Best,