Forum Discussion

Ricardo_Moreira's avatar
Ricardo_Moreira
Frequent Visitor
4 years ago
Solved

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 se...
Need Help
  • SteveHailey's avatar
    SteveHailey
    4 years ago

    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:

    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's avatar
SteveHailey
Icon for Solution Specialist rankSolution Specialist
4 years ago

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. 

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

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

 

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

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:

 

Adjusting the parameter slicer will update the last column:

 


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

-Steve


 

  • Ricardo_Moreira's avatar
    Ricardo_Moreira
    Frequent Visitor
    4 years ago

    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

    • SteveHailey's avatar
      SteveHailey
      Icon for Solution Specialist rankSolution Specialist
      4 years ago

      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:

      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:

       

      • Ricardo_Moreira's avatar
        Ricardo_Moreira
        Frequent Visitor
        4 years ago

        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