Translating INR amount to USD based on user slicer selection

ferdsjoseph · February 2, 2019, 12:07am

Hi,

I have a table in Power BI with a value column in mixed currency - USD and INR and a column that states what currency the value is in - USD or INR. I have a disconnected slicer with values 68, 69, 70, and 71. I want to create a new column called “Amount in USD” where all the INR values will be converted to USD by multiplying with whatever value is selected in the disconnected slicer. How do I do this?

Best Regards,

Ferdinand

GuyJohnson · February 3, 2019, 12:19am

Ferdinand,

Sam has covered this very well in these videos.

Hope this helps.

Guy

ferdsjoseph · February 4, 2019, 2:18am

Thanks for the link Guy. It helped a lot. I think I’m close. My scenario is a bit different though.

Let me describe my data.

My table has records that are either Forecast or Variable Rate (Version column).
Variable Rate version is actually a duplicate of Forecast version where I translated rows where entity = “India” to USD using exchange rates by period.

In the Power BI report, there are 2 disconnected slicers - Version (values: Forecast, Variable Rate) and FX Rate (values: 68, 69, 70, 71)
When Variable Rate version is selected in slicer, the selected value in the FX Rate slicer should be used to translate the INR to USD for all periods.
When Forecast version is selected, no translation needed as the amounts are already in USD.

Am I supposed to use SUMX, CALCULATE, and IF altogether? I can’t seem to figure out the right DAX formula.

Best regards,

Ferdinand

GuyJohnson · February 4, 2019, 12:39pm

Ferdinand,

The best way to attack this may be through the use of variables.

Sam posted the following video on his YouTube channel

Guy

ferdsjoseph · February 4, 2019, 5:17pm

Thank you Guy! I finally have a working formula. My real data actually has 5 entities - 10, 20, 40, 50, & 60 (US, India, UK, Canada, & Australia, respectively). Initially, there’s only Forecast version where VALUE field in all records are in USD. I duplicated it as CONSTANT CURRENCY version but translated India & UK VALUE to local currency (INR & GBP) using monthly exchange rates. On the report, I have 4 disconnected slicers - Version 1, Version 2, Constant Currency INR, & Constant Currency GBP. The constant currency slicers only come into play when Constant Currency is selected in either version. So basically, the Constant Currency version is the Forecast version where India and UK were “re-translated” to USD using the respective constant currency rates. This is the formula that worked for me. There is another for Total Expense Version 2, and then the Variance measure between the 2. I’m hoping there is a more efficient/elegant way of doing it.

Total Expense Version 1 =
IF(SELECTEDVALUE(‘Version1’[Version])=“Constant Currency”
,CALCULATE([Total Value]/SELECTEDVALUE(‘Constant INR’[Rate],70),FILTER(Expense,Expense[Entity]=“20”),FILTER(Expense,Expense[Version]=“Constant Currency”))
+CALCULATE([Total Value]/SELECTEDVALUE(‘Constant GBP’[Rate],.75),FILTER(Expense,Expense[Entity]=“40”),FILTER(Expense,Expense[Version]=“Constant Currency”))
+CALCULATE([Total Value],FILTER(Expense,Expense[Entity]<>“20”),FILTER(Expense,Expense[Entity]<>“40”),FILTER(Expense,Expense[Version]=“Constant Currency”))
,CALCULATE([Total Value],FILTER(Expense,Expense[Version]=SELECTEDVALUE(‘Version1’[Version]))))

Best Regards,

Ferdinand

ferdsjoseph · February 4, 2019, 5:39pm

Here is a couple of screenshots from my report (with actual values, except variances, blanked out). I’m having trouble uploading or pasting them here directly so I’m just sharing the link to them in my google drive.

Forecast vs Constant Currency (where the rates were not changed)
https://drive.google.com/open?id=1l8h_mR6VQ5hxkW2LO_ZDH6wgjRrcPdrQ

Forecast vs Constant Currency (where the rates were changed)
https://drive.google.com/open?id=13UE5rTM0mpAye2DQIcG5RuJERX_hER5Q

Best regards,

Ferdinand

ferdsjoseph · February 4, 2019, 5:56pm

Thank you Guy for the “Variables” tutorial link. I gave it a try and came up with 2 variations. In my scenario, I want to know which among the 3 formula variations is more efficient and why.

Original formula without variables (see in previous reply)
Using Variables, variation#1
Using Variables, variation#2

Using Variables Variation#1:
Total Expense Version 1 =

VAR _CC_Version = 
    CALCULATE([Total Value]/SELECTEDVALUE('Constant INR'[Rate],70),FILTER(Expense,Expense[Entity]="20"),FILTER(Expense,Expense[Version]="Constant Currency"))
    +CALCULATE([Total Value]/SELECTEDVALUE('Constant GBP'[Rate],.75),FILTER(Expense,Expense[Entity]="40"),FILTER(Expense,Expense[Version]="Constant Currency"))
    +CALCULATE([Total Value],FILTER(Expense,Expense[Entity]<>"20"),FILTER(Expense,Expense[Entity]<>"40"),FILTER(Expense,Expense[Version]="Constant Currency"))

`VAR _Non_CC_Version = CALCULATE([Total Value],FILTER(Expense,Expense[Version]=SELECTEDVALUE('Version1'[Version])))`

RETURN
IF(SELECTEDVALUE('Version1'[Version])="Constant Currency"
,_CC_Version
,_Non_CC_Version)

Using Variables Variation#2:
Total Expense Version 1 =

VAR _India = CALCULATE([Total Value]/SELECTEDVALUE('Constant INR'[Rate],70),FILTER(Expense,Expense[Entity]="20"),FILTER(Expense,Expense[Version]="Constant Currency"))

VAR _UK = CALCULATE([Total Value]/SELECTEDVALUE('Constant GBP'[Rate],.75),FILTER(Expense,Expense[Entity]="40"),FILTER(Expense,Expense[Version]="Constant Currency"))

VAR _Other_Entities = CALCULATE([Total Value],FILTER(Expense,Expense[Entity]&lt;&gt;"20"),FILTER(Expense,Expense[Entity]&lt;&gt;"40"),FILTER(Expense,Expense[Version]="Constant Currency"))

VAR _CC_Version = _India + _UK + _Other_Entities

VAR _Non_CC_Version = CALCULATE([Total Value],FILTER(Expense,Expense[Version]=SELECTEDVALUE('Version1'[Version])))

RETURN

IF(SELECTEDVALUE('Version1'[Version])="Constant Currency"
,_CC_Version
,_Non_CC_Version)

Best regards,

Ferdinand

ferdsjoseph · February 4, 2019, 6:38pm

Hi Guy,

It just dawned on me, maybe what I’m trying to achieve doesn’t need the Forecast version to be duplicated as Constant Currency version. Forecast version has 400K+ rows and I am doubling that with my approach. I will try a different approach where I will add an Exchange Rate column to the table. Will post an update when I have a working solution.

Best regards,

Ferdinand

ferdsjoseph · February 4, 2019, 7:21pm

Hi Guy,

Here is my revised formula. I created a monthly exchange rate table and joined it to my Expense table, which now only has Forecast version records, using a calculated XR Key column.

Total Expense Version 1 =

VAR _Divisor = IF(SELECTEDVALUE('Show in Thousands'[Show in Thousands])="Yes",1000,1)

VAR _India = CALCULATE(SUMX(Expense,Expense[Value]*RELATED('Adaptive XR'[Forecast Rate]))/SELECTEDVALUE('Constant INR'[Rate],70),FILTER(Expense,Expense[Entity]="20"),FILTER(Expense,Expense[Version]="Forecast"))

VAR _UK = CALCULATE(SUMX(Expense,Expense[Value]*RELATED('Adaptive XR'[Forecast Rate]))/SELECTEDVALUE('Constant GBP'[Rate],.75),FILTER(Expense,Expense[Entity]="40"),FILTER(Expense,Expense[Version]="Forecast"))

VAR _Other_Entities = CALCULATE(SUMX(Expense,Expense[Value]),FILTER(Expense,Expense[Entity]&lt;&gt;"20"),FILTER(Expense,Expense[Entity]&lt;&gt;"40"),FILTER(Expense,Expense[Version]="Forecast"))

VAR _CC_Version = _India + _UK + _Other_Entities

VAR _Non_CC_Version = CALCULATE(SUMX(Expense,Expense[Value]),FILTER(Expense,Expense[Version]=SELECTEDVALUE('Version1'[Version])))

RETURN

IF(SELECTEDVALUE('Version1'[Version])="Constant Currency"
,_CC_Version
,_Non_CC_Version)/_Divisor

Thank you so much for guiding me in the right direction. Couldn’t have done this without your help. Everyone, please feel free to comment on my approach and formula. Did I use too much variables? Is there a rule of thumb when and when not to use variables?

Best regards,

Ferdinand

GuyJohnson · February 4, 2019, 8:29pm

Ferdinand,

Sorry, I haven’t been near my PC today.

I think the variables approach is the best way to attack a lot of problems in DAX.

Variables make the formula easier to troubleshoot and much less complicated than if everything is in one formula. Variables breakdown the steps into smaller easier chunks that theoretically can be reused.

As to a rule of thumb - there isn’t one as far as I know but Sam may have an idea on this.

I need no thanx you did the heavy lifting.

Guy