Making Slicer on Multiple Categories with Counts

[CuriousGuy001]

Hi Everyone,

I am trying to create a slicer with four selected options "Fruit" , "Vegetable", "Seafood", "Meat" by Count of how many was sold by our Sales Representatives. I got Product Category on row, and Sales Rep. on column. If I select Fruit it will show how many on count was sold by Jack as an example, Jack sold Apple and will count 1 and not counting 0 or empty cells.

[xifeng_L]

Hi @CuriousGuy001 ,

First, you need to convert the data to a one-dimensional table.

Then, you can try below measure.

Measure = CALCULATE(COUNT('Table'[Value]), NOT 'Table'[Value] IN {"0",BLANK()})

Demo - Making Slicer on Multiple Categories with Counts.pbix

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[CuriousGuy001]

Hi Xifeng,

I like this method but it didn't work cause the are duplicate of product category, making more revenues. Thanks!

[xifeng_L]

Sorry, I didn't quite understand your question. If my previous demo doesn't match your expected result, you can tell me the correct result.

[CuriousGuy001]

Hi xifeng,

Don't feel be sorry! so I have 385 data entry in the table, if I use that one dimension (Unpivot Other Column option) will make more data entry in the table, I will get 1540 data entry. I am trying to make a slicer for fruit, vegetable, seafood, meat that can give me a count of our sales rep that has been sold. The result will have to work with the 385 data entry I have, and the one dimensional option won't work.