Solved: Re: How to get Totals in visuals from SELECTEDVALU...

daniq · ‎01-31-2023

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.

daniq · ‎01-31-2023

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

View solution in original post

Anonymous · ‎02-01-2023

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.

View solution in original post

daniq · ‎01-31-2023