Hi,
I have a table where I am trying to display the sum of the values in one currency or the other just by selecting in a slicer.
My data table in power query is like the 1st table (Table1) and my exchange rate table (ExRate) is like the 2nd one
I used the following DAX formula, it works for France but not for USA
Sum of Rebate Settlement value in Selected Currency =
VAR MyRate =
CALCULATE (
FIRSTNONBLANK (Ex_Rate[ExRate],1),
FILTER (
Ex_Rate,
Ex_Rate[TargetCurrency] = SELECTEDVALUE ('Table1'[Values])
&& Ex_Rate[Currency] = SELECTEDVALUE (Ex_Rate[Currency])
)
)
RETURN
SUM ('Table1'[Values]) * MyRate
Can anyone help solving this ?
Thanks
I have a table where I am trying to display the sum of the values in one currency or the other just by selecting in a slicer.
My data table in power query is like the 1st table (Table1) and my exchange rate table (ExRate) is like the 2nd one
Division | Currency | Country | Values |
1 | EUR | France | -104 |
1 | EUR | France | -190 |
1 | EUR | France | -2932 |
2 | EUR | USA | -2498 |
2 | EUR | USA | -2142 |
2 | USD | USA | -4397 |
2 | USD | USA | -2031 |
2 | USD | USA | -2031 |
2 | USD | USA | -2720 |
Country | Currency | TargetCurrency | ExRate |
France | EUR | USD | 1.1 |
France | EUR | EUR | 1 |
USA | USD | USD | 1 |
USA | USD | EUR | 0.89 |
I used the following DAX formula, it works for France but not for USA
Sum of Rebate Settlement value in Selected Currency =
VAR MyRate =
CALCULATE (
FIRSTNONBLANK (Ex_Rate[ExRate],1),
FILTER (
Ex_Rate,
Ex_Rate[TargetCurrency] = SELECTEDVALUE ('Table1'[Values])
&& Ex_Rate[Currency] = SELECTEDVALUE (Ex_Rate[Currency])
)
)
RETURN
SUM ('Table1'[Values]) * MyRate
Can anyone help solving this ?
Thanks