Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ricardo_Moreira
Frequent Visitor

Exchange rate what-if scenario

Dear experts,

 

I am fairly new to Powerbi and I was hoping someone could help me understand the best approach and steps to set an exchange rate what-if scenario in a dashboard to allow me to to see the impact of FX rates fluctuations in my consolidated amounts (example: what would happen to my consolidated figures if the USD FX rate to EUR is 1.5 instead of 1.19).

 

My model looks like this:

Table A - Amounts

Ricardo_Moreira_1-1642685573956.png

 

Table B - FX rates

Ricardo_Moreira_2-1642685612938.png

 

The consolidated amounts in table A are calculated by multiplying the original amounts in table A by the FX rates in table B (by currency). My goal is to set a slicer in the dashboard to allow me to change the FX rate value and see the change updated in my consolidated amounts visual but before that I will need to relate the tables in some way?

 

Thanks in advance, any support will be highly apreciated!

 

Kind regards,

Ricardo Moreira

1 ACCEPTED SOLUTION

Hi Ricardo, you are most welcome.

 

Question 1:

I'm guessing something is wrong with pulling in the FX Rate in the denominator. For troubleshooting, try changing the DAX to just the denominator from the DIVIDE function, so something like: RELATED('FX Rates'[Avg FX Rate] ), to make sure it's pulling in the FX rate from the other table. It should look something like this:

SteveHailey_0-1642780398858.png

If you are seeing blanks still, that makes me think something is perhaps wrong with the relationship. Could you maybe send some screenshots of your relationship from the model view?

 

Question 2:

For sure, you could do something like below. If the currency is "USD" it divides by the value in the parameter slicer, otherwise it divides by 1.

 

Updated Consolidated Amount = 
DIVIDE (
    SUM ( Amounts[Original amount] ),
    IF (
        MAX ( Amounts[Currency] ) = "USD",
        'FX Rate Parameter'[FX Rate Parameter Value],
        1
    )
)

 

The results like this:

SteveHailey_1-1642781193703.png

 

View solution in original post

9 REPLIES 9
Ricardo_Moreira
Frequent Visitor

Hi Steve,
I was hoping you could be so kind to guide me with your knowledge again if you have the chance.

I've created a new what-if scenario for GBP currency and updated the code to:

Updated Consolidated Amount =
DIVIDE (
SUM ( Amounts[Original amount] ),
IF (
MAX ( Amounts[Currency] ) = "USD",
'FX Rate Parameter'[FX Rate USD Parameter Value],
IF (
MAX ( Amounts[Currency] ) = "GBP",
'FX Rate Parameter'[FX Rate GBP Parameter Value],
1
)
)

 

When I change the GBP rate in my slicer, I am able to see the impact in the rows which have exposure to GBP but the Total amount remains the same. Any idea why? 

Thanks in advance.
Kind regards,
Ricardo

Hi @Ricardo_Moreira.
Totals are somewhat tricky in Power BI. And with a what-if parameter you can't create a calculated column. So, I'm sure there's a more elegant solution, and maybe someone else can help you with that. But the solution below should work, and it's what I can think of at this moment.


This is the complete measure:

Updated Consolidated Amount =
IF (
    HASONEVALUE ( Amounts[Amounts_ID] ),
    DIVIDE (
        SUM ( Amounts[Original amount] ),
        IF (
            MAX ( Amounts[Currency] ) = "USD",
            'FX Rate USD Parameter'[FX Rate USD Parameter value],
            IF (
                MAX ( Amounts[Currency] ) = "GBP",
                'FX Rate GBP Parameter'[FX Rate GBP Parameter Value],
                1
            )
        )
    ),
    DIVIDE (
        CALCULATE (
            SUM ( Amounts[Original amount] ),
            'FX rates'[Currency] = "USD"
        ),
        'FX Rate USD Parameter'[FX Rate USD Parameter value]
    )
        + DIVIDE (
            CALCULATE (
                SUM ( Amounts[Original amount] ),
                'FX rates'[Currency] = "GBP"
            ),
            'FX Rate GBP Parameter'[FX Rate GBP Parameter value]
        )
)
    + CALCULATE (
        SUM ( Amounts[Original amount] ),
        'FX rates'[Currency] = "EUR"
    )

 
The IF HASONEVALUE tests to see if the Amounts_ID has one value... if it does, that means it's NOT the total row, and so it uses the usual logic. That's this part of the measure:

IF IT'S NOT THE TOTAL ROW:

IF (
    HASONEVALUE ( Amounts[Amounts_ID] ),
    DIVIDE (
        SUM ( Amounts[Original amount] ),
        IF (
            MAX ( Amounts[Currency] ) = "USD",
            'FX Rate USD Parameter'[FX Rate USD Parameter value],
            IF (
                MAX ( Amounts[Currency] ) = "GBP",
                'FX Rate GBP Parameter'[FX Rate GBP Parameter Value],
                1
            )
        )
    ),


If it doesn't have one value, and thus IS the total row, it sums the original amounts on USD rows then and divides that sum by the USD parameter, sums the original amounts on the GBP rows and divides that sum by the GBP parameter, sums the EUR original amounts. And adds those three sums together. That's this part of the measure:

IF IT IS THE TOTAL ROW:

DIVIDE (
        CALCULATE (
            SUM ( Amounts[Original amount] ),
            'FX rates'[Currency] = "USD"
        ),
        'FX Rate USD Parameter'[FX Rate USD Parameter value]
    )
        + DIVIDE (
            CALCULATE (
                SUM ( Amounts[Original amount] ),
                'FX rates'[Currency] = "GBP"
            ),
            'FX Rate GBP Parameter'[FX Rate GBP Parameter value]
        )
)
    + CALCULATE (
        SUM ( Amounts[Original amount] ),
        'FX rates'[Currency] = "EUR"
    )

 

SteveHailey_0-1644353924527.png

 

You'll need to adjust the parameter names in the DAX. In the sample .pbix that I made (which you can downloade here), I split the GBP and USD parameters into separate tables, while it looks like you had them in one table.


Again, there's probably a better way, but I'm super busy today and this is the best I can do at the moment. I wanted to get back to you at least with something that works. 

Hi Steve,

Many many thanks for getting back with an answer to my question.

I initially thought it would be as easy as nesting a new IF in the code but apparently not.

I will try your solution and give an update on the result as soon as I have the chance.

Kind regards,

Ricardo

Hi Steve,

The solution you offer is working fine and it requires a final adjustment to ensure the EUR amounts do not change since they are not exposed to any FX rate variation.
Kind regards,
Ricardo

Ricardo_Moreira_0-1644570789685.png

 

Hi @Ricardo_Moreira. Glad to hear it's working fine. My bad on the EUR amounts. You just need to remove the ", 1" in the upper part of the measure. Here it is, below, with that removed. There's an updated .pbix file here.

 

Updated Consolidated Amount =
IF (
    HASONEVALUE ( Amounts[Amounts_ID] ),
    DIVIDE (
        SUM ( Amounts[Original amount] ),
        IF (
            MAX ( Amounts[Currency] ) = "USD",
            'FX Rate USD Parameter'[FX Rate USD Parameter value],
            IF (
                MAX ( Amounts[Currency] ) = "GBP",
                'FX Rate GBP Parameter'[FX Rate GBP Parameter Value]
            )
        )
    ),
    DIVIDE (
        CALCULATE (
            SUM ( Amounts[Original amount] ),
            'FX rates'[Currency] = "USD"
        ),
        'FX Rate USD Parameter'[FX Rate USD Parameter value]
    )
        + DIVIDE (
            CALCULATE (
                SUM ( Amounts[Original amount] ),
                'FX rates'[Currency] = "GBP"
            ),
            'FX Rate GBP Parameter'[FX Rate GBP Parameter value]
        )
)
    + CALCULATE (
        SUM ( Amounts[Original amount] ),
        'FX rates'[Currency] = "EUR"
    )

 

SteveHailey_0-1644593221059.png

 

SteveHailey
Solution Specialist
Solution Specialist

Hello @Ricardo_Moreira

Here is what I would do. I attached a .pbix file here that you can download.

To relate the tables, in Power Query I created a unique ID column in each table by concatenating the Scenario, Period, and Currency columns. I added a dash between each just for better readability. 

SteveHailey_0-1642728526830.png

SteveHailey_1-1642728547557.png

I then created a one-to-many relationship on those newly created ID columns:

SteveHailey_2-1642728609112.png

I then created a calculated column in the Amounts table using thise code:

 

Consolidated Amount = 
DIVIDE( Amounts[Original amount], RELATED('FX Rates'[Avg FX Rate] ) )

 

SteveHailey_3-1642728672399.png

I then created a What-If parameter via Home > Modeling > New parameter:

SteveHailey_4-1642728843995.png

I then created a new measure using this DAX formula: 

 

Updated Consolidated Amount = 
DIVIDE( SUM( Amounts[Original amount] ), 'FX Rate Parameter'[FX Rate Parameter Value] )

 

And then added all the fields to a table:

SteveHailey_7-1642729263187.png

 

Adjusting the parameter slicer will update the last column:

SteveHailey_8-1642729287207.png

 


I hope that helps! Let me know if you have any questions.

-Steve


 

Hi Steve,

Thanks very much for reaching out, your post is much helpful.

I understand the logic you are following and applied it to my case.

Just a couple of questions:

1- The calculated column "Consolidated amount"is returning blanks instead of intended amounts, any idea why?

2- This slicer is applying a variation in both currencies, EUR and USD. Is there a way to have the variance applied to only one of the currencies?

 

Kind regards,

Ricardo

Hi Ricardo, you are most welcome.

 

Question 1:

I'm guessing something is wrong with pulling in the FX Rate in the denominator. For troubleshooting, try changing the DAX to just the denominator from the DIVIDE function, so something like: RELATED('FX Rates'[Avg FX Rate] ), to make sure it's pulling in the FX rate from the other table. It should look something like this:

SteveHailey_0-1642780398858.png

If you are seeing blanks still, that makes me think something is perhaps wrong with the relationship. Could you maybe send some screenshots of your relationship from the model view?

 

Question 2:

For sure, you could do something like below. If the currency is "USD" it divides by the value in the parameter slicer, otherwise it divides by 1.

 

Updated Consolidated Amount = 
DIVIDE (
    SUM ( Amounts[Original amount] ),
    IF (
        MAX ( Amounts[Currency] ) = "USD",
        'FX Rate Parameter'[FX Rate Parameter Value],
        1
    )
)

 

The results like this:

SteveHailey_1-1642781193703.png

 

Hi Steve,

Your solution is working amazingly now with the IF condition, it is doing exactly what I was hoping for in my dashboard. Thank you so much for taking the time to help me out, I really appreciated it.

Wish you a great year!

Kind regards,

Ricardo

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors