Convert from multiple currencies to USD or EUR using Slicer

[dmelhem]

Hello,

I'm trying to add a slicer to my report, allowing users to select EUR or USD. Based on their selection, I will convert the value of my "License Amount" field to EUR or USD.

Here is my Data Model:

Table 1 : Opportunity

Columns: OPP ID, LicenseFee, Currency

Table 2: ExchangeTable

SourceCurrency, TargetCurrency, ExchangeRate

I managed to implement the formula correctly without a slicer, and converting to 1 of the 2 currencies, but when i start adding conditions based on a slicer it is not working.

Here is the formula that is working with 1 currency only: 

Discounted License EUR = OPPORTUNITY[LICENSEFEE]*LOOKUPVALUE(ExchangeTable[EXCHANGERATE],ExchangeTable[SOURCECURRENCY],Opportunity[CURRENCY],ExchangeTable[TARGETCURRENCY],"EUR")

Appreciate your help,

Thanks,

Dory

[Zubair_Muhammad · Accepted solution]

Hi @dmelhem

Try this Measure in your Opportunity Table

Add slicer from ExchangeTable[TargetCurrency] and select a target currency

Measure =
VAR MyRate =
    CALCULATE (
        FIRSTNONBLANK ( ExchangeTable[ExchangeRate], 1 ),
        FILTER (
            ExchangeTable,
            ExchangeTable[SourceCurrency] = SELECTEDVALUE ( Opportunity[Currency] )
                && ExchangeTable[TargetCurrency] = SELECTEDVALUE ( ExchangeTable[TargetCurrency] )
        )
    )
RETURN
    SUM ( Opportunity[LicenseFee] ) * MyRate

[Zubair_Muhammad · Accepted solution]

Hi @dmelhem

Try this Measure in your Opportunity Table

Add slicer from ExchangeTable[TargetCurrency] and select a target currency

Measure =
VAR MyRate =
    CALCULATE (
        FIRSTNONBLANK ( ExchangeTable[ExchangeRate], 1 ),
        FILTER (
            ExchangeTable,
            ExchangeTable[SourceCurrency] = SELECTEDVALUE ( Opportunity[Currency] )
                && ExchangeTable[TargetCurrency] = SELECTEDVALUE ( ExchangeTable[TargetCurrency] )
        )
    )
RETURN
    SUM ( Opportunity[LicenseFee] ) * MyRate

[dmelhem]

Hi Zubair,

Sorry to bother again.

Your solution indeed, worked as a measure, but now that i'm moving forward with the building Reports, i noticed that i am unable to do "Sum" on measures (sorry i'm kind of new to this).

Do you know if there is any way i can get the same result but with a column?

Thanks,

Dory

[Zubair_Muhammad · Accepted solution]

@dmelhem

Since you need dynamic translation based on slicer selection, I believe a CALCULATED COLUMN won't work.

But we can write another MEASURE to get the Totals as well

Let me know if it works

Measure 2 =
IF (
    HASONEVALUE ( Opportunity[OPP ID] ),
    [Measure],
    SUMX ( ALLSELECTED ( Opportunity[OPP ID] ), [Measure] )
)

[Zubair_Muhammad]

@dmelhem

Please see the attached file here

[dmelhem]

Thanks a lot Zubair, it worked!!!

Really appreciate it 🙂