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.
Hi community!
I'm trying to make a multi currency report, based on a slicer. I have a table with the currency rates by months:
On the other hand, I have a table with the currency and amout, wich I merge with the previous one, based on the month. Right there I have the columns calculiting each amount for the rate of that month.
Now, the issue is in the visuals, I can't get the totals:
This is the formula (pardon the little mess, I'm pointing the important things to look):
Solved! Go to Solution.
Ok, answering myself, the measure was almost correct (maybe not that elegant at all), this is the final version:
Hi @daniq ,
I think you can try code as below.
Dynamic Amount =
VAR ReportingCurrency =
SELECTEDVALUE ( 'Currency Rates'[Currency] )
VAR SourceCurrency =
SELECTEDVALUE ( 'Client Forecasting Delivery'[Currency] )
VAR Amount =
SUM ( 'Client Forecasting Delivery'[Forecast Amount with sign] )
VAR ARSRate =
AVERAGE ( 'Client Forecasting Delivery'[ARS RATE] )
VAR BRLRate =
AVERAGE ( 'Client Forecasting Delivery'[BRL RATE] )
VAR CLPRate =
AVERAGE ( 'Client Forecasting Delivery'[CLP RATE] )
VAR COPRate =
AVERAGE ( 'Client Forecasting Delivery'[COP Rate] )
VAR MXNRate =
AVERAGE ( 'Client Forecasting Delivery'[MXN RATE] )
VAR PENRate =
AVERAGE ( 'Client Forecasting Delivery'[PEN RATE] )
VAR USDRate =
AVERAGE ( 'Client Forecasting Delivery'[USD Rate] )
RETURN
SWITCH (
SourceCurrency,
"ARS",
SWITCH (
ReportingCurrency,
"ARS", Amount * ARSRate,
"BRL", Amount * BRLRate,
"CLP", Amount * CLPRate,
"COP", Amount * COPRate,
"MXN", Amount * MXNRate,
"PEN", Amount * PENRate,
"USD", Amount * USDRate,
BLANK ()
),
"BRL",
SWITCH (
ReportingCurrency,
"ARS", Amount * ARSRate,
"BRL", Amount * BRLRate,
"CLP", Amount * CLPRate,
"COP", Amount * COPRate,
"MXN", Amount * MXNRate,
"PEN", Amount * PENRate,
"USD", Amount * USDRate,
BLANK ()
),
"CLP",
SWITCH (
ReportingCurrency,
"ARS", Amount * ARSRate,
"CLP", Amount * CLPRate,
"COP", Amount * COPRate,
"MXN", Amount * MXNRate,
"PEN", Amount * PENRate,
"USD", Amount * USDRate,
BLANK ()
),
"COP",
SWITCH (
ReportingCurrency,
"ARS", Amount * ARSRate,
"BRL", Amount * BRLRate,
"CLP", Amount * CLPRate,
"COP", Amount * COPRate,
"MXN", Amount * MXNRate,
"PEN", Amount * PENRate,
"USD", Amount * USDRate,
BLANK ()
),
"MXN",
SWITCH (
ReportingCurrency,
"ARS", Amount * ARSRate,
"BRL", Amount * BRLRate,
"CLP", Amount * CLPRate,
"COP", Amount * COPRate,
"MXN", Amount * MXNRate,
"PEN", Amount * PENRate,
"USD", Amount * USDRate,
BLANK ()
),
"PEN",
SWITCH (
ReportingCurrency,
"ARS", Amount * ARSRate,
"BRL", Amount * BRLRate,
"CLP", Amount * CLPRate,
"COP", Amount * COPRate,
"MXN", Amount * MXNRate,
"PEN", Amount * PENRate,
"USD", Amount * USDRate,
BLANK ()
),
"USD",
SWITCH (
ReportingCurrency,
"ARS", Amount * ARSRate,
"BRL", Amount * BRLRate,
"CLP", Amount * CLPRate,
"COP", Amount * COPRate,
"MXN", Amount * MXNRate,
"PEN", Amount * PENRate,
"USD", Amount * USDRate,
BLANK ()
),
0
)
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ok, answering myself, the measure was almost correct (maybe not that elegant at all), this is the final version:
Hi @daniq ,
I think you can try code as below.
Dynamic Amount =
VAR ReportingCurrency =
SELECTEDVALUE ( 'Currency Rates'[Currency] )
VAR SourceCurrency =
SELECTEDVALUE ( 'Client Forecasting Delivery'[Currency] )
VAR Amount =
SUM ( 'Client Forecasting Delivery'[Forecast Amount with sign] )
VAR ARSRate =
AVERAGE ( 'Client Forecasting Delivery'[ARS RATE] )
VAR BRLRate =
AVERAGE ( 'Client Forecasting Delivery'[BRL RATE] )
VAR CLPRate =
AVERAGE ( 'Client Forecasting Delivery'[CLP RATE] )
VAR COPRate =
AVERAGE ( 'Client Forecasting Delivery'[COP Rate] )
VAR MXNRate =
AVERAGE ( 'Client Forecasting Delivery'[MXN RATE] )
VAR PENRate =
AVERAGE ( 'Client Forecasting Delivery'[PEN RATE] )
VAR USDRate =
AVERAGE ( 'Client Forecasting Delivery'[USD Rate] )
RETURN
SWITCH (
SourceCurrency,
"ARS",
SWITCH (
ReportingCurrency,
"ARS", Amount * ARSRate,
"BRL", Amount * BRLRate,
"CLP", Amount * CLPRate,
"COP", Amount * COPRate,
"MXN", Amount * MXNRate,
"PEN", Amount * PENRate,
"USD", Amount * USDRate,
BLANK ()
),
"BRL",
SWITCH (
ReportingCurrency,
"ARS", Amount * ARSRate,
"BRL", Amount * BRLRate,
"CLP", Amount * CLPRate,
"COP", Amount * COPRate,
"MXN", Amount * MXNRate,
"PEN", Amount * PENRate,
"USD", Amount * USDRate,
BLANK ()
),
"CLP",
SWITCH (
ReportingCurrency,
"ARS", Amount * ARSRate,
"CLP", Amount * CLPRate,
"COP", Amount * COPRate,
"MXN", Amount * MXNRate,
"PEN", Amount * PENRate,
"USD", Amount * USDRate,
BLANK ()
),
"COP",
SWITCH (
ReportingCurrency,
"ARS", Amount * ARSRate,
"BRL", Amount * BRLRate,
"CLP", Amount * CLPRate,
"COP", Amount * COPRate,
"MXN", Amount * MXNRate,
"PEN", Amount * PENRate,
"USD", Amount * USDRate,
BLANK ()
),
"MXN",
SWITCH (
ReportingCurrency,
"ARS", Amount * ARSRate,
"BRL", Amount * BRLRate,
"CLP", Amount * CLPRate,
"COP", Amount * COPRate,
"MXN", Amount * MXNRate,
"PEN", Amount * PENRate,
"USD", Amount * USDRate,
BLANK ()
),
"PEN",
SWITCH (
ReportingCurrency,
"ARS", Amount * ARSRate,
"BRL", Amount * BRLRate,
"CLP", Amount * CLPRate,
"COP", Amount * COPRate,
"MXN", Amount * MXNRate,
"PEN", Amount * PENRate,
"USD", Amount * USDRate,
BLANK ()
),
"USD",
SWITCH (
ReportingCurrency,
"ARS", Amount * ARSRate,
"BRL", Amount * BRLRate,
"CLP", Amount * CLPRate,
"COP", Amount * COPRate,
"MXN", Amount * MXNRate,
"PEN", Amount * PENRate,
"USD", Amount * USDRate,
BLANK ()
),
0
)
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |