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

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

 

 

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. 

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