cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fro88er
Helper III
Helper III

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?

 

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)])
 
CurrencyToggleCurrency.JPGCurrencyToggleCAD.JPG
1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

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

Apply Exchange Rates to Local Currency button.JPG

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
yingyinr
Community Support
Community Support

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

Apply Exchange Rates to Local Currency button.JPG

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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.

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
Super User IV
Super User IV

@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)])

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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)
 
CurrencyToggleCurrency4.JPG

@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!!


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




lbendlin
Super User III
Super User III

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors