Forum Discussion
Create selectable categories for numeric slicer
I'm trying to create a slicer that will sort numeric data into one of three categories that can be selected by radio button or checkbox:
- All
- 20+
- 50+
My data currently looks something like this:
| Incident ID | Total Impact |
| ABC | 0 |
| EFG | 12 |
| HIJ | 145 |
| KLM | 51 |
| NOP | 26 |
| ETC | 800 |
When I create a slicer based on the Impact column, it produces a slider, and since I have some high outliers, it's a ridiculously wide range, making the dragging function basically useless. I'm only interested in those three categories above. Is there a way to create them? Many thanks!
Hi ReadTheIron ,
I agreed with amitchandak 's suggestion—— You have to create an independent table using those three values.
And I have entered a table as shown below:
Then please use the following formula to create a flag measure:
Flag = VAR _sum = CALCULATE ( SUM ( 'Table'[Total Impact] ), ALLEXCEPT ( 'Table', 'Table'[Incident ID] ) ) RETURN SWITCH ( MAX ( 'ForSlicer'[Catrgory] ), "20+", IF ( _sum > 20, 1 ), "50+", IF ( _sum > 50, 1 ), "All", 1 )Apply it to filter pane ,set as "is 1", the final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
ReadTheIron , You have to create an independent table using those three values. You can use enter data
Then based on selected value you can return the measure
Switch(selectedvales(Bucket[Bucket]) ,
"All", sum(Table[Total Impact]),
"20+",calculate( sum(Table[Total Impact]), filter(Table,Table[Total Impact] >=20)) ,
"50+",calculate( sum(Table[Total Impact]), filter(Table,Table[Total Impact] >=50))
)
Try a measure like above
Thanks for the response, amitchandak ! I'm still quite new to Power BI so I'm having some trouble figuring out the last step. I've created the measure, then created a slicer with Buckets[Buckets] as the selected data field. It looks just like what I need!
However, when I make a selection, the graphics on the page don't change. They will change based on the slider based on the Impact field. Is there another connection I need to be making?
Hi ReadTheIron ,
I agreed with amitchandak 's suggestion—— You have to create an independent table using those three values.
And I have entered a table as shown below:
Then please use the following formula to create a flag measure:
Flag = VAR _sum = CALCULATE ( SUM ( 'Table'[Total Impact] ), ALLEXCEPT ( 'Table', 'Table'[Incident ID] ) ) RETURN SWITCH ( MAX ( 'ForSlicer'[Catrgory] ), "20+", IF ( _sum > 20, 1 ), "50+", IF ( _sum > 50, 1 ), "All", 1 )Apply it to filter pane ,set as "is 1", the final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
