Forum Discussion
How to filter multiple measures for top/mid/low range based on Names selected in 3 different slicers
Hello! I am trying to have a table with opportunities and the value, but have 3 measures for Total ARR based on the selections made in the 3 different slicers. I made a simple report to show the idea, but broken out into 3 tables. I need it all in 1 table.
-Opportunity names selected in "Top Range" slicer would filter everything.
-Opportunity names selected in "Mid Range" slicer would filter "Mid Range" and "Low Range".
-Opportunity names selected in "Low Range" slicer would only filter Low Range.
I made copies of the "Name" column so I would have "Name", "Name 2", "Name 3"
This is the basic concept where Opp 1-9 are selected in "Top Range", and all tables are filtered to show Opps 1-9. Then I filtered "Mid Range" to only show Opp 1-6, which filters Mid and Low Range tables to only Opps 1-6. Then I filter "Low Range" to only show OPP 1-3 and it filters the table only to Opps 1-3. Again a basic concept of what I am trying to do.
Basic Logic
The end goal is to have 1 table with the Opp Names and 3 measures for [Top Range ARR], [Mid Range ARR], [Low Range ARR].
Example 1: If I select Opp 3 in "Top Range" but not in "Mid Range" then the [Mid Range ARR] would show 0 or be blank for that opp and not be included in the total [Mid Range ARR]. It would also exclude it from "Low Range" slicer and [Low Range ARR]
Example of slicer filtering: Unselecting an opp in "Top Range" would make the name disapear from "Mid Range" and "Low Range".
Picture of end goal: (notice slicers being filtered by higher level, and the totals) (data for screenshot was faked to be able to show what I need)
Please help me! I just feel like this has to be possible. Thank you!
2 Replies
hi hello789567 ,
is it necessary to use 3 slicer for the Opportunity Name or is it even ok only 1?
Can you please share some sample data of your table in text form? Only the screenshots are not enough for me.
It doesn't have to be 3 slicers, but I do need a way for the user to be able to select what they mark/filter as Top, Mid, and Low range, so I thought it was nessesary. Open to ideas. It doesn't even have to have the duplicated Name columns, that was just the way I was trying to get it to work.
Here is the sample data I was posting screen shots of in original post.EDIT: I don't think this table is working right. I just did "Opp ID" 1-40, "Name" Opp 1-Opp 40, duplicated name for "Name 2" and "Name 3", and "ARR" is just a random number between 200,000-1,000,000.
Opp ID Name Name 2 Name 3 ARR 1 Opp 1 Opp 1 Opp 1 603157 2 Opp 2 Opp 2 Opp 2 861819 3 Opp 3 Opp 3 Opp 3 218570 4 Opp 4 Opp 4 Opp 4 389878 5 Opp 5 Opp 5 Opp 5 379003 6 Opp 6 Opp 6 Opp 6 697019 7 Opp 7 Opp 7 Opp 7 325887 8 Opp 8 Opp 8 Opp 8 555303 9 Opp 9 Opp 9 Opp 9 164829 10 Opp 10 Opp 10 Opp 10 29786 11 Opp 11 Opp 11 Opp 11 417037 12 Opp 12 Opp 12 Opp 12 586612 13 Opp 13 Opp 13 Opp 13 849123 14 Opp 14 Opp 14 Opp 14 115247 15 Opp 15 Opp 15 Opp 15 992911 16 Opp 16 Opp 16 Opp 16 713453 17 Opp 17 Opp 17 Opp 17 297521 18 Opp 18 Opp 18 Opp 18 621365 19 Opp 19 Opp 19 Opp 19 957907 20 Opp 20 Opp 20 Opp 20 712479 21 Opp 21 Opp 21 Opp 21 549085 22 Opp 22 Opp 22 Opp 22 778902 23 Opp 23 Opp 23 Opp 23 936451 24 Opp 24 Opp 24 Opp 24 596444 25 Opp 25 Opp 25 Opp 25 195701 26 Opp 26 Opp 26 Opp 26 365824 27 Opp 27 Opp 27 Opp 27 365482 28 Opp 28 Opp 28 Opp 28 186828 29 Opp 29 Opp 29 Opp 29 381082 30 Opp 30 Opp 30 Opp 30 614462 31 Opp 31 Opp 31 Opp 31 991920 32 Opp 32 Opp 32 Opp 32 936098 33 Opp 33 Opp 33 Opp 33 240992 34 Opp 34 Opp 34 Opp 34 885260 35 Opp 35 Opp 35 Opp 35 854604 36 Opp 36 Opp 36 Opp 36 501579 37 Opp 37 Opp 37 Opp 37 909026 38 Opp 38 Opp 38 Opp 38 790102 39 Opp 39 Opp 39 Opp 39 720204 40 Opp 40 Opp 40 Opp 40 399962
