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:

     

Ricardo_Moreira's avatar
Ricardo_Moreira
Frequent Visitor
4 years ago

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

Ricardo_Moreira's avatar
Ricardo_Moreira
Frequent Visitor
4 years ago

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

 

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

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