Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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] ) )
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
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
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] ) )
Thanks a lot Zubair, it worked!!!
Really appreciate it 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |