Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I want to use a measure to evaluate whether a value in a row is an outlier, but I can't get it to work.
I have created the following measure:
Outlier =
IF (
'Estimate vs actual'[Variance EvA]
>= CALCULATE ( [LCL], ALLSELECTED ( 'Estimate vs actual' ) )
&& 'Estimate vs actual'[Variance EvA]
<= CALCULATE ( [UCL], ALLSELECTED ( 'Estimate vs actual' ) ),
"No Outlier",
"Outlier"
)
This measure consist out of the following calculated column and measures:
MEASURE: UCL = CALCULATE( [Mean] + [STDEV] * (2,66) )
As you can see in the below table, only the first two instances are considered an outlier, whereas I expect every instance above 54,78% to be considered an outlier. I have a date slicer active, no other outside filters.
I have tried to follow the steps mentioned in this post https://community.powerbi.com/t5/Desktop/Evaluate-row-value-to-measure-simple-example/m-p/600079#M28..., but that did not solve my problem.
What am I doing wrong here? Thanks for your help.
Solved! Go to Solution.
Hi @swestendorp
the Outlier-"Measure" isn't a valid syntax for a measure, as it is missing aggregation-commands. With "SUM" as aggregation it would look like so and work as a measure:
Outlier =
VAR UCL =
CALCULATE(
AVERAGEX( 'Estimate vs actual', 'Estimate vs actual'[Variance EvA] ) -- Mean
+ STDEV.S( 'Estimate vs actual'[Variance EvA] ) -- STDEV
* (2,66),
ALLSELECTED( 'Estimate vs actual' )
)
VAR LCL =
CALCULATE(
AVERAGEX( 'Estimate vs actual', 'Estimate vs actual'[Variance EvA] ) -- Mean
- STDEV.P( 'Estimate vs actual'[Variance EvA] ) -- STDEV
* (2,66),
ALLSELECTED( 'Estimate vs actual' )
)
RETURN
IF (
SUM('Estimate vs actual'[Variance EvA])
>= LCL
&& SUM('Estimate vs actual'[Variance EvA])
<= UCL ,
"No Outlier",
"Outlier"
)
So you'd probably have used it as a column? Then of course, the ALLSELECTED wouldn't work, as it would only work on measures (columns values are calculated during load of the data model and are not aware of any filters on the report).
Please check out the attached file where I mocked up some things that should give food for thought (it's got some loose ends that might force you to rethink your requirements).
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @swestendorp
Using variables should do the job here:
Outlier =
VAR LCL = [LCL]
VAR UCL = [UCL]
Return
IF (
'Estimate vs actual'[Variance EvA] >= LCL
&& 'Estimate vs actual'[Variance EvA] <= UCL,
"No Outlier",
"Outlier"
)
ALLSELECTED is a beast: https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @ImkeF !
Thanks so much for taking the time to look at my issue.
The solution is working when I remove the date filter. So when I keep the date filter active, it still does not return the desired outcome. That's why I added the ALLSELECTED, to take into account my date filter. How can i fix this?
Best,
Sifra
Hi @swestendorp
what is shown in the picture you've posted?
The column values for UCL and LCL look alright? if they evluate the right boundaries, then using their expressions in the variables should return the desired result.
Also ALLSELECTED might interfere with each other. So please check if ALLSELECTED is used in any of the referenced measures.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @ImkeF
Thanks again for your help.
The upper and lower control limit need to be calculated basis a set of rows, in this case all observations in the past year, and is the same for each observation. Every row should be evaluated basis this static upper & lower control limit. For each observation outside those limits I want to label it as "Outlier" or "No outlier". Graphically it looks like this.
I fail to understand your solution, because when I remove the ALLSELECTED part from either the MEAN measure or the STDEV measure, I get a different values for UCL & LCL for each row. Where should I add the ALLSELECT part?
Your UCL would look like so: Everything in the first argument will be filtered by ALLSELECTED. But ALLSELECTED is just used once:
VAR UCL =
CALCULATE(
AVERAGEX( 'Estimate vs actual', 'Estimate vs actual'[Variance EvA] ) -- Mean
+ STDEV.S( 'Estimate vs actual'[Variance EvA] ) -- STDEV
* (2,66),
ALLSELECTED( 'Estimate vs actual' )
)
VAR LCL =
CALCULATE(
AVERAGEX( 'Estimate vs actual', 'Estimate vs actual'[Variance EvA] ) -- Mean
- STDEV.S( 'Estimate vs actual'[Variance EvA] ) -- STDEV
* (2,66),
ALLSELECTED( 'Estimate vs actual' )
)
..
RETURN
IF (
'Estimate vs actual'[Variance EvA]
>= LCL
&& 'Estimate vs actual'[Variance EvA]
<= UCL,
"No Outlier",
"Outlier"
)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hang on: Why do you apply ALLSELECTED to the whole (fact) table instead just on the Date-column of your calendar table?
.. if you're using the INDEX-column from your fact table on your chart, you should use ALLSELECTED just on that column.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @ImkeF
I am at a loss here. I've done as you suggested, but still I get only the right results when I remove the date filter. I also get results for rows that should be filtered out.
Outlier =
VAR UCL =
CALCULATE(
AVERAGEX( 'Estimate vs actual', 'Estimate vs actual'[Variance EvA] ) -- Mean
+ STDEV.S( 'Estimate vs actual'[Variance EvA] ) -- STDEV
* (2,66),
ALLSELECTED( 'Estimate vs actual' )
)
VAR LCL =
CALCULATE(
AVERAGEX( 'Estimate vs actual', 'Estimate vs actual'[Variance EvA] ) -- Mean
- STDEV.P( 'Estimate vs actual'[Variance EvA] ) -- STDEV
* (2,66),
ALLSELECTED( 'Estimate vs actual' )
)
RETURN
IF (
'Estimate vs actual'[Variance EvA]
>= LCL
&& 'Estimate vs actual'[Variance EvA]
<= UCL ,
"No Outlier",
"Outlier"
)
From which table does your date-filter come from?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @swestendorp
the Outlier-"Measure" isn't a valid syntax for a measure, as it is missing aggregation-commands. With "SUM" as aggregation it would look like so and work as a measure:
Outlier =
VAR UCL =
CALCULATE(
AVERAGEX( 'Estimate vs actual', 'Estimate vs actual'[Variance EvA] ) -- Mean
+ STDEV.S( 'Estimate vs actual'[Variance EvA] ) -- STDEV
* (2,66),
ALLSELECTED( 'Estimate vs actual' )
)
VAR LCL =
CALCULATE(
AVERAGEX( 'Estimate vs actual', 'Estimate vs actual'[Variance EvA] ) -- Mean
- STDEV.P( 'Estimate vs actual'[Variance EvA] ) -- STDEV
* (2,66),
ALLSELECTED( 'Estimate vs actual' )
)
RETURN
IF (
SUM('Estimate vs actual'[Variance EvA])
>= LCL
&& SUM('Estimate vs actual'[Variance EvA])
<= UCL ,
"No Outlier",
"Outlier"
)
So you'd probably have used it as a column? Then of course, the ALLSELECTED wouldn't work, as it would only work on measures (columns values are calculated during load of the data model and are not aware of any filters on the report).
Please check out the attached file where I mocked up some things that should give food for thought (it's got some loose ends that might force you to rethink your requirements).
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @ImkeF,
Thanks for bearing with me 🙂
This is the situation (=not correct) when I have a date slicer active (which I want)
This is what it look's like (= correct) when I remove that date filter (which I don't want)
Best,
Sifra
Hi
rewrite your measure so that you just have on ALLSELECTED in the complete chain. As far as I can see, this means that you cannot reuse the existing MEAN and STDEV, but have to re-state their logic in your "Outlier"-measure, with just one ALLSELECTED in the CALCULATE-wrapper.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |