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 Friends,
I have two tables. One is currency conversion_rate table and second is the fact table. They are both joined on Currency.
I want to build a slicer (or toggle) by which I can select the currency and the Price gets converted using the conversion rate from the other table.
Simple expected solution - I do not want a full all to all conversion. I only want X currency(any) Vs EUR toggle (in the example GBP and EUR). Every Price should be able to convert in to EUR by clicking somewhere (like on a toggle slicer)
Thanks.
Here are the tables
Currency | CONVERSION_RATE |
ARS | 73.25684257 |
BDT | 82.30264599 |
BGN | 1.827857452 |
CAD | 1.495088615 |
CHF | 0.953521944 |
CLP | 871.2290893 |
CNY | 6.850970883 |
COP | 3619.889557 |
CZK | 23.6299849 |
DKK | 6.450662402 |
EUR | 0.87831384 |
GBP | 0.733943605 |
HKD | 9.870237762 |
HUF | 343.4156459 |
INR | 83.06085896 |
MXN | 28.26554894 |
NOK | 14.76276735 |
PLN | 3.688233902 |
RON | 2.920294554 |
RSD | 83.95242482 |
SEK | 7.83124562 |
TWD | 31.78877778 |
USD | 1.052703153 |
Product | Currency | Price | New Measure |
A | GBP | 10 | |
B | GBP | 15 | |
C | GBP | 12 | |
D | GBP | 14 | |
E | GBP | 15 | |
F | GBP | 10 | |
G | GBP | 20 | |
H | GBP | 12 |
// Keep XRates disconnected. Assuming that
// the table stores rates of conversion from
// the indicated currency to some other, but common,
// currency. In other words, the rate tells us
// the amount of the currency that you have to pay for
// 1 unit of the target currency (whatever it is,
// but for this calculation it does not matter in the
// slightest).
//
// Here's a measure that will display totals
// in any currency you want...
[Total] =
IF( HASONEFILTER( XRates[Currency] ),
var __targetCurr = SELECTEDVALUE( XRates[Currency] )
var __targetCurrXrate = SELECTEDVALUE( XRates[xrate] )
return
SUMX(
// Iterating over currencies in the current
// context to speed up the calculation. If
// we iterate over products there'll be much
// more computation. There are far fewer currencies
// than products, right?
VALUES( 'Product'[Currency] ),
// get the currency of the
// products we right now iterate over
var __originalCurr = Product[Currency]
// got the xrate for this currency
var __originalCurrXrate =
CALCULATE(
SELECTEDVALUE( XRates[xrate] ),
XRates[Currency] = __originalCurr,
ALL( XRates )
)
// get the total price of the products
// with the current currency
var __total = CALCULATE( SUM( 'Product'[Price] ) )
// express the total in the original xrate
var __valueInCommonCurr =
__total * __originalCurrXrate
// express the above total in the target currency
var __valueInTargetCurr =
__valueInCommonCurr / __targetCurrXrate
return
__valueInTargetCurr
)
)
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 |
---|---|
43 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |