Sum value based on Selected filters

Question

Hi Team,&nbsp;I am trying to show different values based on the currency selection.&nbsp;Here are the RulesSum Vol :1) IF 1 currency is selected than show sum of 1 currency2) IF&nbsp;1 or more currency is selected than show sum of EURconverted3) IF nothing is selected than show All Currency&nbsp;The dax below works for rule 1 and 3 but not 2.I have tried using a count curreny dax :&nbsp;Currency Count( ranking) = COUNTROWS(SUMMARIZE('Dealer Ranking','Dealer Ranking'[Currency]))but this doesn't seem to work.&nbsp;Here is my dax&nbsp;Ranking Size (Vol) =VAR Allcurrency = &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; [Ranking Size],&nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; 'Dealer Ranking',&nbsp; &nbsp; &nbsp; &nbsp; 'Dealer Ranking'[Type] = "Vol. (MM)" &amp;&amp;'Dealer Ranking'[All] =0&nbsp; &nbsp; &nbsp; &nbsp; ))VAR ALLEUR = &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; [Ranking Size],&nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; 'Dealer Ranking',&nbsp; &nbsp; &nbsp; &nbsp; 'Dealer Ranking'[Type] = "Vol. (MM)" &amp;&amp;'Dealer Ranking'[All] = 1&nbsp; &nbsp; &nbsp; &nbsp; ))VAR ALLUSD = &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; [Ranking Size],&nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; 'Dealer Ranking',&nbsp; &nbsp; &nbsp; &nbsp; 'Dealer Ranking'[Type] = "Vol. (MM)" &amp;&amp;'Dealer Ranking'[All] = 2&nbsp; &nbsp; &nbsp; &nbsp; ))VAR ALLGBP = &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; [Ranking Size],&nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; 'Dealer Ranking',&nbsp; &nbsp; &nbsp; &nbsp; 'Dealer Ranking'[Type] = "Vol. (MM)" &amp;&amp;'Dealer Ranking'[All] = 3&nbsp; &nbsp; &nbsp; &nbsp; )) RETURNSWITCH(&nbsp; &nbsp; TRUE(),&nbsp; &nbsp; SELECTEDVALUE('Dealer Ranking'[Currency]) = "ALL (EUR)", ALLEUR,&nbsp; &nbsp; SELECTEDVALUE('Dealer Ranking'[Currency]) = "ALL (USD)", ALLUSD,&nbsp; &nbsp; SELECTEDVALUE('Dealer Ranking'[Currency]) = "ALL (GBP)", ALLGBP,&nbsp; &nbsp; Allcurrency&nbsp;&nbsp;)&nbsp;

anonymous · Accepted Answer

Hi&nbsp;ViralPatel212&nbsp;
&nbsp;
For your question, here is the method I provided:
&nbsp;
Here's some dummy data
&nbsp;
"Dealer Ranking"

&nbsp;
Create a measure.&nbsp;Count the total number of selected slicers.
&nbsp;
Currency Count = 
    COUNTROWS(
        VALUES('Dealer Ranking'[Currency])
    )

Create a measure.&nbsp;Calculate the sum of Ranking Size.
&nbsp;
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)
&nbsp;
Here is the result.

&nbsp;

&nbsp;
Regards,
Nono Chen
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.
&nbsp;
&nbsp;
&nbsp;
&nbsp;

Forum Discussion

Sum value based on Selected filters

2 Replies

Related content

Selecting a slicer source field based on filtered value

Selected-Filtered value calculation

Dynamic filters - Selected Value - DAX

filter table based on selected value using contains

Default value when no filter selected