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

     

swestendorp's avatar
swestendorp
Helper I
6 years ago

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

 

 

ImkeF's avatar
ImkeF
Community Champion
6 years ago

From which table does your date-filter come from?

 

  • ImkeF's avatar
    ImkeF
    Community Champion
    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).

     

  • swestendorp's avatar
    swestendorp
    Helper I
    6 years ago

    Thanks a lot ImkeF for your help!