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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

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