topic Re: How to get Totals in visuals from SELECTEDVALUE in DAX Commands and Tips

How to get Totals in visuals from SELECTEDVALUE

daniq — Tue, 31 Jan 2023 14:38:12 GMT

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

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 = SUM('Client Forecasting Delivery'[ARS RATE])

VAR BRLRate = SUM('Client Forecasting Delivery'[BRL RATE])

VAR CLPRate = SUM('Client Forecasting Delivery'[CLP RATE])

VAR COPRate = SELECTEDVALUE('Client Forecasting Delivery'[COP Amount With Sign])

VAR MXNRate = SUM('Client Forecasting Delivery'[MXN RATE])

VAR PENRate = SUM('Client Forecasting Delivery'[PEN RATE])

VAR USDRate = SELECTEDVALUE('Client Forecasting Delivery'[USD AMount with Signt])

RETURN IF(ReportingCurrency = "ARS" && SourceCurrency = "USD", CALCULATE(Amount * ARSRate), IF(ReportingCurrency = "BRL" && SourceCurrency = "USD", CALCULATE(Amount * BRLRate), IF(ReportingCurrency = "CLP" && SourceCurrency = "USD", CALCULATE(Amount * CLPRate), IF(ReportingCurrency = "COP" && SourceCurrency = "USD", COPRate,IF(ReportingCurrency = "MXN" && SourceCurrency = "USD", CALCULATE(Amount * MXNRate), IF(ReportingCurrency = "PEN" && SourceCurrency = "USD", CALCULATE(Amount * PENRate), IF(ReportingCurrency = "USD" && SourceCurrency = "USD", USDRate)))))))

Any ideas would be appreciated.

Re: How to get Totals in visuals from SELECTEDVALUE

daniq — Tue, 31 Jan 2023 23:34:28 GMT

Ok, answering myself, the measure was almost correct (maybe not that elegant at all), this is the final version:

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

IF(ReportingCurrency = "ARS" && SourceCurrency = "ARS", Amount * ARSRate,

IF(ReportingCurrency = "BRL" && SourceCurrency = "ARS", Amount * BRLRate,

IF(ReportingCurrency = "CLP" && SourceCurrency = "ARS", Amount * CLPRate,

IF(ReportingCurrency = "COP" && SourceCurrency = "ARS", Amount * COPRate,

IF(ReportingCurrency = "MXN" && SourceCurrency = "ARS", Amount * MXNRate,

IF(ReportingCurrency = "PEN" && SourceCurrency = "ARS", Amount * PENRate,

IF(ReportingCurrency = "USD" && SourceCurrency = "ARS", Amount * USDRate,

IF(ReportingCurrency = "ARS" && SourceCurrency = "BRL", Amount * ARSRate,

IF(ReportingCurrency = "BRL" && SourceCurrency = "BRL", Amount * BRLRate,

IF(ReportingCurrency = "CLP" && SourceCurrency = "BRL", Amount * CLPRate,

IF(ReportingCurrency = "COP" && SourceCurrency = "BRL", Amount * COPRate,

IF(ReportingCurrency = "MXN" && SourceCurrency = "BRL", Amount * MXNRate,

IF(ReportingCurrency = "PEN" && SourceCurrency = "BRL", Amount * PENRate,

IF(ReportingCurrency = "USD" && SourceCurrency = "BRL", Amount * USDRate,

IF(ReportingCurrency = "ARS" && SourceCurrency = "CLP", Amount * ARSRate,

IF(ReportingCurrency = "BRL" && SourceCurrency = "USD", Amount * BRLRate,

IF(ReportingCurrency = "CLP" && SourceCurrency = "CLP", Amount * CLPRate,

IF(ReportingCurrency = "COP" && SourceCurrency = "CLP", Amount * COPRate,

IF(ReportingCurrency = "MXN" && SourceCurrency = "CLP", Amount * MXNRate,

IF(ReportingCurrency = "PEN" && SourceCurrency = "CLP", Amount * PENRate,

IF(ReportingCurrency = "USD" && SourceCurrency = "CLP", Amount * USDRate,

IF(ReportingCurrency = "ARS" && SourceCurrency = "COP", Amount * ARSRate,

IF(ReportingCurrency = "BRL" && SourceCurrency = "COP", Amount * BRLRate,

IF(ReportingCurrency = "CLP" && SourceCurrency = "COP", Amount * CLPRate,

IF(ReportingCurrency = "COP" && SourceCurrency = "COP", Amount * COPRate,

IF(ReportingCurrency = "MXN" && SourceCurrency = "COP", Amount * MXNRate,

IF(ReportingCurrency = "PEN" && SourceCurrency = "COP", Amount * PENRate,

IF(ReportingCurrency = "USD" && SourceCurrency = "COP", Amount * USDRate,

IF(ReportingCurrency = "ARS" && SourceCurrency = "MXN", Amount * ARSRate,

IF(ReportingCurrency = "BRL" && SourceCurrency = "MXN", Amount * BRLRate,

IF(ReportingCurrency = "CLP" && SourceCurrency = "MXN", Amount * CLPRate,

IF(ReportingCurrency = "COP" && SourceCurrency = "MXN", Amount * COPRate,

IF(ReportingCurrency = "MXN" && SourceCurrency = "MXN", Amount * MXNRate,

IF(ReportingCurrency = "PEN" && SourceCurrency = "MXN", Amount * PENRate,

IF(ReportingCurrency = "USD" && SourceCurrency = "MXN", Amount * USDRate,

IF(ReportingCurrency = "ARS" && SourceCurrency = "PEN", Amount * ARSRate,

IF(ReportingCurrency = "BRL" && SourceCurrency = "PEN", Amount * BRLRate,

IF(ReportingCurrency = "CLP" && SourceCurrency = "PEN", Amount * CLPRate,

IF(ReportingCurrency = "COP" && SourceCurrency = "PEN", Amount * COPRate,

IF(ReportingCurrency = "MXN" && SourceCurrency = "PEN", Amount * MXNRate,

IF(ReportingCurrency = "PEN" && SourceCurrency = "PEN", Amount * PENRate,

IF(ReportingCurrency = "USD" && SourceCurrency = "PEN", Amount * USDRate,

IF(ReportingCurrency = "ARS" && SourceCurrency = "USD", Amount * ARSRate,

IF(ReportingCurrency = "BRL" && SourceCurrency = "USD", Amount * BRLRate,

IF(ReportingCurrency = "CLP" && SourceCurrency = "USD", Amount * CLPRate,

IF(ReportingCurrency = "COP" && SourceCurrency = "USD", Amount * COPRate,

IF(ReportingCurrency = "MXN" && SourceCurrency = "USD", Amount * MXNRate,

IF(ReportingCurrency = "PEN" && SourceCurrency = "USD", Amount * PENRate,

IF(ReportingCurrency = "USD" && SourceCurrency = "USD", Amount * USDRate))))))))))))))))))))))))))))))))))))))))))))))))) + 0

Re: How to get Totals in visuals from SELECTEDVALUE

Anonymous — Wed, 01 Feb 2023 09:37:29 GMT

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.