How to filter/slice values outside a +/-5% threshold without a calculated column?

[cillejs]

Hi all,

I have a measure called Ratio that compares actual time to a recommended time for each machine. The interpretation is:

• Ratio > 1 --> machine is slower than recommended
• Ratio < 1 --> machine is faster than recommended

I've been asked to flag only values that fall outside a 5% tolerance band i.e.:

• Upper outliers: Ratio > 1,05
• Lower outliers: Ratio < 0,95

Requirements

1. I have to try and avoid creating a calculated column
2. A solution could be:
   1. One measure that returns only the upper values (> 1,05)
   2. One measure that returns only the lower values (< 0,95)
3. It could be a slicer or maybe even a button or two where you can only see those outside the threshold. 

Thank you! 😄

Example of how the filter/slicer could look: 

Example of data:

[v-aatheeque]

Hi @cillejs 

We wanted to follow up to check if you’ve had an opportunity to review the previous responses. If you require further assistance, please don’t hesitate to let us know

[v-aatheeque]

Hi @cillejs 

Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[amitchandak]

@cillejs , A calculated column can not use slicer value. You can create a measure. If required a line level measure using sumx or countx 

Sumx(Table, Table[QTy]* [Selected Efficiency Ratio] )

Sumx(Table, Table[QTy]* [Selected Efficiency Ratio] ) 

Where Selected Efficiency Ratio is measure created for selected value of Parameter 

of you can use MIn and max  Value 

Min Val = Minx(allelected(' Efficiency Ratio'), ' Efficiency Ratio'[Value]) 

Max Val = Maxx(allelected(' Efficiency Ratio'), ' Efficiency Ratio'[Value]) 

Again, these two can be used in a measure 

[cillejs]

@amitchandak thank you for your quick reply.

I am not sure I understand🙂

I want to be able to toggle between the threshold and ALL values-

I want a fixed +-5% value, not a dynamic range picker.

Thank you

[amitchandak]

@cillejs , Upper and lower bounds meausres

Lower = Minx(allelected(' Efficiency Ratio'), ' Efficiency Ratio'[Value])*.95

Upper = Maxx(allelected(' Efficiency Ratio'), ' Efficiency Ratio'[Value]) *.105

More than Upper = Sumx(Filter(Table, [Measure] >[Upper]), [Measure]) 

Less than Lower = Sumx(Filter(Table, [Measure] <[Lower] ), [Measure]) 

Assume you already have measure of column to check value with