Forum Discussion

hpatel247's avatar
hpatel247
Icon for Helper I rankHelper I
8 years ago

create measure based on multiple slicer selections

Hi All,

 

My data has a list of contacts received each month. Within that, i have 2 additional columns, Progress further with Yes, No and unknown options and other column called RAG, with RED, AMBER and GREEN options.

 

I am using 'Progress Further' and 'RAG' as slicer values for the users to select.

 

Once selected, my measure will perform calculations based on those values. e.g i have 20 contacts where 15 say 'Yes' in the progress further column.

 

And in the RAG column, i have 3 (2 where progress further is Yes) that say RED, 12 (10 that say Yes) that say AMBER and 4 (3 that say Yes) that say Green.

 

So the measures i want are:

 

% Contacts Progressed Further = 15/20 = 75%

% Contacts where RAG is RED = 3/20 = 15%

% Contacts where RAG is RED and Progress Further is Yes = 2/15 = 13.3%

% Contacts where RAG is AMBER = 12/20 = 60%

% Contacts where RAG is AMBER and Progress Further is Yes = 10/15 = 66.7%

% Contacts where RAG is GREEN = 4/20 = 20%

% Contacts where RAG is GREEN and Progress Further is Yes = 3/15 = 20%

 

Therefore, i want 1 graph with a measure which when selecting an option on the Progress Further Slicer would give you and outturn and if you also select an option from the RAG slicer, the % would change accordingly.

 

Is this even possible?

 

Any help is greatly appreciated.

 

kind regards

Hetal

 

 

Need Help

3 Replies

    • hpatel247's avatar
      hpatel247
      Icon for Helper I rankHelper I
      8 years ago

      Hi Greg,

       

      Thank you for your email. Below is my sample data:

       

      Contact IDContact DateProgress Further?RAG
      101/06/2018YesAMBER
      202/06/2018YesRED
      303/06/2018YesAMBER
      405/06/2018NoAMBER
      505/06/2018YesAMBER
      606/06/2018YesGREEN
      707/06/2018NoAMBER
      812/06/2018YesAMBER
      914/06/2018YesGREEN
      1015/06/2018NoAMBER
      1118/06/2018YesAMBER
      1219/06/2018YesAMBER
      1320/06/2018YesRED
      1422/06/2018YesAMBER
      1523/06/2018NoGREEN
      1624/06/2018YesAMBER
      1725/06/2018YesAMBER
      1828/06/2018YesGREEN
      1928/06/2018NoRED
      2030/06/2018YesAMBER

       

      So to summarise, i have 20 contacts in total of which 15 say 'Yes' in the Progress Further? column and so on.

       

      On my visual, i have the following slicers:

       

       

      Now if possible what i want to do is, if you just select Yes from the Progress Further slicer, i should calculate the % of all contacts where progress is Yes therefore the % should be 15/20 = 75%. 

       

      If the user only selects AMBER on the RAG slicer, it should calculate the % as 13/20 = 65%

       

      If the user selects Yes from the Progress Further slicer and GREEN on the RAG slicer, it should calculate % as 3/15 = 20%

       

      I know this may be asking a lot but wondered if there is a way to do this and if so how. Possible that you may need a big IF statement but i can't figure out how to reference the filtered values. I can create a measure that would show me if there was a filter on a slicer using ISFILTERED and SELECTEDVALUE but cannot apply this within the IF statement

       

      Apologies if this is still unclear.

       

      kind regards

       

      Hetal

       

      kind regards

      • v-piga-msft's avatar
        v-piga-msft
        Icon for Resident Rockstar rankResident Rockstar
        8 years ago

        Hi hpatel247,

         

         I know this may be asking a lot but wondered if there is a way to do this and if so how. Possible that you may need a big IF statement but i can't figure out how to reference the filtered values. I can create a measure that would show me if there was a filter on a slicer using ISFILTERED and SELECTEDVALUE but cannot apply this within the IF statement

         

        We could use ISFILTERED and SELECTEDVALUE in If statement. You could have a reference of the fomula below.

         

        numerator =
IF (
    ISFILTERED ( 'table'[Progress Further?] ),
    CALCULATE (
        COUNT ( 'table'[Progress Further?] ),
        FILTER (
            'table',
            'table'[Progress Further?] = SELECTEDVALUE ( 'table'[Progress Further?] )
        )
    ),
    IF (
        ISFILTERED ( 'table'[RAG] ),
        CALCULATE (
            COUNT ( 'table'[RAG] ),
            FILTER ( 'table', 'table'[RAG] = SELECTEDVALUE ( 'table'[RAG] ) )
        )
    )
)
        Measure =
IF (
    AND ( ISFILTERED ( 'table'[Progress Further?] ), ISFILTERED ( 'table'[RAG] ) ),
    CALCULATE (
        DIVIDE (
            'table'[numerator],
            IF (
                AND ( ISFILTERED ( 'table'[Progress Further?] ), ISFILTERED ( 'table'[RAG] ) ),
                CALCULATE (
                    COUNT ( 'table'[Progress Further?] ),
                    FILTER (
                        ALL ( 'table' ),
                        'table'[Progress Further?] = SELECTEDVALUE ( 'table'[Progress Further?] )
                    )
                )
            )
        )
    )
)

         

        Best  Regards,

        Cherry