Apply Exchange Rates to Local Currency button

[Fro88er]

I have request to add a simple Currency Button (Local/USD). When USD is selected it applies the respected currency rate.  I have built a Currency Table (disconnected). The Temp Revenue (selected currency) meaure works, however it is appying the CAD currency rate accross all of the regions not just Toronto. There is a currency ID on the Fact Table, call it USD and CAD or Currency ID, 1 and 2). Basicly, two field buttons [Local] no filter, [USD] apply currency rates.

Q: How to write this measure?

Temp Revenue (local) =

CALCULATE(

SUM('Fact1Staff Consolidated GP'[Temp Bill Amount]))

Temp Revenue (selected currency) =

SWITCH(TRUE(),

[Selected Currency]="CAD", [Temp Revenue (local)]*VALUES(DimCurrency[Currency Rate]),

[Temp Revenue (local)])

[Anonymous · Accepted solution]

Hi @Fro88er ,

Please update the formula of measure as below:

Temp Revenue (selected currency) = 
VAR _selcurrency =
    SELECTEDVALUE ( 'DimCurrency'[Currency] )
VAR _currate =
    CALCULATE (
        MAX ( 'DimCurrency'[Currency Rate] ),
        'DimCurrency'[Currency] = _selcurrency
    )
RETURN
    IF (
        _selcurrency = "CAD"
            && MAX ( 'Fact1Staff Consolidated GP'[Region] ) = "Toronto",
        [Temp Revenue (local)] * _currate,
        [Temp Revenue (local)]
    )

Best Regards

Rena

[Fro88er]

Rena,

After closer examination the totals do not add up. Additionally, is there a solution that does not reference the Region and just let the CurrencyID drive the logic? The reason, I also have an By Office, By Practice, tables, plus if we add another CAD city/region I have to modify the code.

[Fro88er]

Yes, this worked perfectly! I will rename my buttons to better reflect what is going on!  Thank you so much, the timing could not be better! 

[Greg_Deckler]

@Fro88er - I had to read this through a few times. Sample data would have helped to make this clearer. However, if I understand correctly, you want to implement a currency conversion for non-US cities. If that is the case you could do this:

Temp Revenue (selected currency) =
  VAR __SelectedCurrency = [Selected Currency]
  VAR __CityCurrency = MAX('Fact1StaffConsolidatedGP'[CurrencyID])
  VAR __Rate = MAX('Currency Table'[Currency Rate])
RETURN
  IF(__SelectedCurrency = __CityCurrency,[Temp Revenue (local)]*__Rate,[Temp Revenue (local)])

[Fro88er]

I am have the meaure working with the variable [Temp Revenue (selected currency)] but the total do not match.  When I write a seperate measure [Temp Revenue Test] the numbers and totals match. However, when I try and add/merge into only 1 measure, the [Temp Revenue (selected currency)] that includes the variables with a Sumx function the totals don't match either, what am I doing wrong?

Temp Revenue (selected currency) =

VAR __SelectedCurrency = 2

VAR __CityCurrency = MAX('Fact1Staff Consolidated GP'[ExchangeRateID])

VAR __Rate = MAX(DimCurrency[Currency Rate])

RETURN

IF(__SelectedCurrency = __CityCurrency,[Temp Revenue (local)]*__Rate,[Temp Revenue (local)])

Temp Revenue Test =

SUMX(

'Fact1Staff Consolidated GP',[Temp Revenue (selected currency)])

xTemp Revenue (Selected Currency)GD 2 =

VAR __SelectedCurrency = 2

VAR __CityCurrency = MAX('Fact1Staff Consolidated GP'[ExchangeRateID])

VAR __Rate = MAX(DimCurrency[Currency Rate])

Var VarTotal = IF(__SelectedCurrency = __CityCurrency,[Temp Revenue (local)]*__Rate,[Temp Revenue (local)])

Var VarTable = SUMMARIZE('Fact1Staff Consolidated GP','Fact1Staff Consolidated GP'[1Staff Office.Region],"RegionTempRevenue",VarTotal)

RETURN

SUMX(VarTable,VarTotal)

[Greg_Deckler]

@Fro88er This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Sorry I lost track of this, I don't check my messages on this site very often!!

[amitchandak]

@Fro88er ,Two way 

One You can use this like measure slicer and switch the measure

https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

Second refer these
https://www.youtube.com/watch?v=4dosxfNxR6M
https://www.sqlbi.com/articles/currency-conversion-in-power-bi-reports/
https://radacad.com/currency-converter-power-bi-function-part-1
https://www.youtube.com/watch?v=Q4hF4CwJsFEhttps://radacad.com/currency-exchange-rate-conversion-in-...
https://businessintelligist.com/2015/03/17/power-bi-tutorial-how-to-implement-currency-conversion-us...
https://blog.beyondimpactllc.com/blog/building-a-dynamic-currency-converter-with-power-bi

[lbendlin]

Your SWITCH() statement needs to test for Toronto, not for CAD.