Forum Discussion

swestendorp's avatar
swestendorp
Helper I
6 years ago
Solved

Use measure to evaluate row value

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: 

 

CALCULATED COLUMN: Variance EvA = IF( 'Estimate vs actual'[Estimate?] = "Estimate", ('Estimate vs actual'[ActualCost] - 'Estimate vs actual'[EstimateCost] ) / 'Estimate vs actual'[EstimateCost], BLANK () )

MEASURE: UCL = CALCULATE( [Mean] + [STDEV] * (2,66) )

MEASURE: LCL = CALCULATE( [Mean] - [STDEV] * (2,66) )
MEASURE: Mean = CALCULATE( AVERAGEX( 'Estimate vs actual', 'Estimate vs actual'[Variance EvA] ), ALLSELECTED( 'Estimate vs actual' ) )
MEASURE: STDEV = CALCULATE( STDEV.S( 'Estimate vs actual'[Variance EvA] ), ALLSELECTED( 'Estimate vs actual' ) )
 

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#M285437, but that did not solve my problem. 

 

What am I doing wrong here? Thanks for your help. 

 

 

Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    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).

     

12 Replies

    • swestendorp's avatar
      swestendorp
      Helper I
      6 years ago

      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

      • ImkeF's avatar
        ImkeF
        Community Champion
        6 years ago

        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.