Category report slicer based on measures

[Anonymous]

Hi Community,

My dataset looks like following:

Staff	Hrs1	Hrs2	Hrs3	Hrs4	Hrs5	Hrs 6
A	1	4	7	1	4	8
B	2	3	5	2	3	5
C	0	9	3	0	9	3
D	7	8	2	7	8	4
E	6	5	7	6	5	9
F	2	7	7	2	7	7

I created following measures:

Indv Hrs = Hrs1+ Hrs3+ Hrs4
Group Hrs Hrs2+ Hrs5+ Hrs6

On the report I have following table:

Staff	Indv Hrs	Group Hrs
A	9	16
B	9	11
C	3	21
D	16	20
E	19	19
F	11	21

I want to include a slicer at the top of the report that provides selection of 'Indv Hrs' & 'Group Hrs'. If I do so by including the above two measures on the slicer it comes up with all the calulations. I want just the categories 'Indv Hrs' & 'Group Hrs' listed on the slicer. Based on the selection Indv Hrs or Group Hrs show respectively.

Please suggest how can I achieve this.

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Slicer:

You may create a measure as below.

Result = 
SWITCH(
    SELECTEDVALUE(Slicer[Category]),
    "Indv Hrs",
    SUM('Table'[Hrs1])+SUM('Table'[Hrs3])+SUM('Table'[Hrs4]),
    "Group Hrs",
    SUM('Table'[Hrs2])+SUM('Table'[Hrs5])+SUM('Table'[Hrs 6])
)

Finally you need to use the matrix visual to display the result.

Best Regards

Allan

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

[amitchandak]

@Anonymous , Are you looking for measure slicer

measure slicer
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
https://www.youtube.com/watch?v=vlnx7QUVYME