create measure based on multiple slicer selections

[hpatel247]

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

[Greg_Deckler]

So, are you saying you want 7 measures or 1 measure because you say you want 7 measures but then it seems like you want just a single measure in the visual. Can you mock up the results you want? Can you post sample data?

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[hpatel247]

Hi Greg,

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

Contact ID	Contact Date	Progress Further?	RAG
1	01/06/2018	Yes	AMBER
2	02/06/2018	Yes	RED
3	03/06/2018	Yes	AMBER
4	05/06/2018	No	AMBER
5	05/06/2018	Yes	AMBER
6	06/06/2018	Yes	GREEN
7	07/06/2018	No	AMBER
8	12/06/2018	Yes	AMBER
9	14/06/2018	Yes	GREEN
10	15/06/2018	No	AMBER
11	18/06/2018	Yes	AMBER
12	19/06/2018	Yes	AMBER
13	20/06/2018	Yes	RED
14	22/06/2018	Yes	AMBER
15	23/06/2018	No	GREEN
16	24/06/2018	Yes	AMBER
17	25/06/2018	Yes	AMBER
18	28/06/2018	Yes	GREEN
19	28/06/2018	No	RED
20	30/06/2018	Yes	AMBER

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]

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