Forum Discussion
How to filter/slice values outside a +/-5% threshold without a calculated column?
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
- I have to try and avoid creating a calculated column
- A solution could be:
- One measure that returns only the upper values (> 1,05)
- One measure that returns only the lower values (< 0,95)
- 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:
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
6 Replies
- amitchandak
Super User
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
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
- Ashish_Mathur
Hi,
Share the download link of the PBI file.
v-aatheequeCommunity Support
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.
