Forum Discussion

Fro88er's avatar
Fro88er
Helper IV
5 years ago
Solved

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 curr...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
Fro88er
Helper IV
5 years ago

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's avatar
Greg_Deckler
Community Champion
5 years ago

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