Forum Discussion
Apply Exchange Rates to Local Currency button
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?
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
8 Replies
Your SWITCH() statement needs to test for Toronto, not for CAD.
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-slicer-parameter-table-pattern
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115Second 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-power-bi-with-live-rates-part-2
https://businessintelligist.com/2015/03/17/power-bi-tutorial-how-to-implement-currency-conversion-using-power-query-and-power-pivot/
https://blog.beyondimpactllc.com/blog/building-a-dynamic-currency-converter-with-power-biFro88er - 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)])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 = 2VAR __CityCurrency = MAX('Fact1Staff Consolidated GP'[ExchangeRateID])VAR __Rate = MAX(DimCurrency[Currency Rate])RETURNIF(__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 = 2VAR __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)RETURNSUMX(VarTable,VarTotal)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/547907Sorry I lost track of this, I don't check my messages on this site very often!!
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
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!
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.
