Sum value based on Selected filters

[ViralPatel212]

Hi Team,

I am trying to show different values based on the currency selection.

Here are the Rules

Sum Vol :

1) IF 1 currency is selected than show sum of 1 currency

2) IF 1 or more currency is selected than show sum of EURconverted

3) IF nothing is selected than show All Currency 

The dax below works for rule 1 and 3 but not 2.

I have tried using a count curreny dax : 

Currency Count( ranking) = COUNTROWS(SUMMARIZE('Dealer Ranking','Dealer Ranking'[Currency]))

but this doesn't seem to work.

Here is my dax

Ranking Size (Vol) =

VAR Allcurrency =

    CALCULATE(

        [Ranking Size],

        FILTER(

        'Dealer Ranking',

        'Dealer Ranking'[Type] = "Vol. (MM)" &&

'Dealer Ranking'[All] =0

        )

)

VAR ALLEUR =

    CALCULATE(

        [Ranking Size],

        FILTER(

        'Dealer Ranking',

        'Dealer Ranking'[Type] = "Vol. (MM)" &&

'Dealer Ranking'[All] = 1

        )

)

VAR ALLUSD =

    CALCULATE(

        [Ranking Size],

        FILTER(

        'Dealer Ranking',

        'Dealer Ranking'[Type] = "Vol. (MM)" &&

'Dealer Ranking'[All] = 2

        )

)

VAR ALLGBP =

    CALCULATE(

        [Ranking Size],

        FILTER(

        'Dealer Ranking',

        'Dealer Ranking'[Type] = "Vol. (MM)" &&

'Dealer Ranking'[All] = 3

        )

)

RETURN

SWITCH(

    TRUE(),

    SELECTEDVALUE('Dealer Ranking'[Currency]) = "ALL (EUR)", ALLEUR,

    SELECTEDVALUE('Dealer Ranking'[Currency]) = "ALL (USD)", ALLUSD,

    SELECTEDVALUE('Dealer Ranking'[Currency]) = "ALL (GBP)", ALLGBP,

    Allcurrency

  )

[Anonymous · Accepted solution]

Hi @ViralPatel212 

For your question, here is the method I provided:

Here's some dummy data

"Dealer Ranking"

Create a measure. Count the total number of selected slicers.

Currency Count = 
    COUNTROWS(
        VALUES('Dealer Ranking'[Currency])
    )

Create a measure. Calculate the sum of Ranking Size.

Ranking Size (Vol) = 
VAR CurrencyCount = [Currency Count]
VAR SelectedCurrency = IF(HASONEVALUE('Dealer Ranking'[Currency]), SELECTEDVALUE('Dealer Ranking'[Currency]), BLANK())
VAR SumSingleCurrency =
    CALCULATE(
        SUM('Dealer Ranking'[Ranking Size]),
        'Dealer Ranking'[Currency] = SelectedCurrency
    )
VAR SumEURConverted =
    SUMX(
        'Dealer Ranking', 
        [Ranking Size]
    )
VAR SumAllCurrencies =
    CALCULATE(
        SUM('Dealer Ranking'[Ranking Size]),
        ALL('Dealer Ranking'[Currency])
    )
RETURN
    SWITCH(
        TRUE(),
        CurrencyCount = 1, SumSingleCurrency,
        CurrencyCount = 2, SumEURConverted,
        SumAllCurrencies)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bolfri]

Can you share some sample data to work with? 🙂