Forum Discussion
Threshold Slicer
Hi,
I'm trying to create a threshold slicer for a table view to filter orders by the total revenue. Below is an example of a data set that has the revenue and gross profit broken out by product for each order. Is there a way to create a slicer to filter for the order if the total revenue of all lines are <2500 or >=2500? So for example, if I selected the <2500 value in the slicer, only order TRG789 would show in the table view.
| Order | Product | Revenue | Gross Profit |
| MGK123 | Phone | 100 | 15 |
| MGK123 | TV | 5,000 | 250 |
| MGK123 | Ipad | 400 | 70 |
| PWB456 | Tire | 600 | 90 |
| PWB456 | Engine | 2,000 | 150 |
| TRG789 | Printer | 300 | 25 |
Hi Anonymous
(1)Create a column to count the total revenue group by Order .
total Revenue = CALCULATE(SUM('Table'[Revenue]),ALLEXCEPT('Table','Table'[Order]))(2)Create a column to judge whether the total revenue is less than 2500 .If it is ,return 1 ,if not ,return 0 .
judge = IF('Table'[total Revenue]<2500,1,0)(3)Enter data to create a new table with judgement condition and value .
(4)Create a relationship between two tables ,filter from judgement condition table to revenue table.
(5)Add a slicer with field “condition” .The final result is as shown :
I have attached my pbix file ,you can refer to it .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Anonymous , You can create a what-if measure, and then you can have a measure or all measures like
R1 = sum(Table[Revenue])
filtered R1= //based on what if param
var _min= minx(allselected(param), param [param])
var _max = max(allselected(param), param [param])
return
sumx(filter( summarize(Table, [Order], [product], "_1", [R1]), [_1] >=_min && [_1]<=_max),[_1])
Hi Anonymous
(1)Create a column to count the total revenue group by Order .
total Revenue = CALCULATE(SUM('Table'[Revenue]),ALLEXCEPT('Table','Table'[Order]))(2)Create a column to judge whether the total revenue is less than 2500 .If it is ,return 1 ,if not ,return 0 .
judge = IF('Table'[total Revenue]<2500,1,0)(3)Enter data to create a new table with judgement condition and value .
(4)Create a relationship between two tables ,filter from judgement condition table to revenue table.
(5)Add a slicer with field “condition” .The final result is as shown :
I have attached my pbix file ,you can refer to it .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
