Filter by Benchmark

[Sdhn420]

Hi all,

I need to filter the margin of a table using the bechmarck selected from the slicer.

Example - table should display Margin% >=Slicer Selected Value1 && margin% <=Slicer selected value

Sample file attached.

https://drive.google.com/file/d/13YqsUiXwqNLFOd-J6FJW3gMHSrtkHVDQ/view?usp=sharing

[v-eqin-msft · Accepted solution]

Hi @Sdhn420 ,

Please use the following formula to create a measure:

Measure =
IF (
    MAX ( 'Data'[Margin%] )
        >= MIN ( 'Benchmark'[Criteria] ) * 100
        && MAX ( 'Data'[Margin%] )
            <= MAX ( 'Benchmark'[Criteria] ) * 100,
    1,
    0
)

And apply it to filter pane,set as "=1" like this:

Here is the pbix file.

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.

[Vera_33]

Hi @Sdhn420 

Add a measure

test = 
VAR minC = MINX(VALUES(Benchmark[Criteria]),[Criteria])*100
VAR maxC = MAXX(VALUES(Benchmark[Criteria]),[Criteria])*100
RETURN
MAXX(FILTER(Data,Data[Margin%] >=minC&&Data[Margin%]<=maxC),[Margin%])

[amitchandak]

@Sdhn420 , Add a what if parameter with a range slcier and try a new measure

new measure =
var _min = minx(allselected(whatif), whatif[value])
var _max = maxx(allselected(whatif), whatif[value])
return
calculate([Margin %], filter(Values(Table[material]), [Margin %] >=_min && [Margin %] <=_max))