cancel
Showing results for
Did you mean:
Highlighted
Helper V

## Evaluate row value to measure - simple example

Hi,

Im trying to evaluate each column value agaisnt the measure but cant seem to figure out why it is struggling.

A very simple 1 column table (Test);

Number

---------

1

2

3

4

5

6

7

8

9

10

Measure = STDEV.P(Test[Number]) - which gives a Standard deviation value of 2.87

I have now a calculated column that checks to see iff each of the values in the number column is greater than or less than the Standard Deviation calculated in the measure using this formula;

LogicalTest = IF(Test[Number]<[Measure], "Less", "Greater")

But the resulting table looks like this;

Why is it evaluating each row value as "Greater"? Surely values 1 and 2 should show "Less"

See the PBI file below:

https://www.dropbox.com/s/kyrfej1luf8uwan/MeasureVsColumn.pbix?dl=0

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver IV

## Re: Evaluate row value to measure - simple example

Usually, measures adapt according to the scope it is used for, so it will change to the standard deviation of the current row being evaluated and will always give an incorrect result. To overcome this you need to fix the calculation to estimate the value over all the values in the column. Use this one: STD = calculate(STDEV.P(Table1[Number]),ALL(Table1[Number])) Then do your check against this measure.
5 REPLIES 5
Highlighted
Super User III

## Re: Evaluate row value to measure - simple example

@rax99

Measures transform row context to Filter context

So your STDEV.P gets calculated for each individual row in above formula

Try

`LogicalTest = IF(Test[Number]<STDEV.P(Test[Number]), "Less", "Greater")`

Regards,
Zubair

Highlighted
Helper V

## Re: Evaluate row value to measure - simple example

Thanks for that.

The only issue I have is that the Standard deviation needs to be stored as a measure. And from there it needs to evaluate each row to that measure value. Can this be achieved anyhow?

Highlighted
Resolver IV

## Re: Evaluate row value to measure - simple example

Usually, measures adapt according to the scope it is used for, so it will change to the standard deviation of the current row being evaluated and will always give an incorrect result. To overcome this you need to fix the calculation to estimate the value over all the values in the column. Use this one: STD = calculate(STDEV.P(Table1[Number]),ALL(Table1[Number])) Then do your check against this measure.
Highlighted
Super User III

## Re: Evaluate row value to measure - simple example

@rax99

If you want to keep the original measure you can use

```LogicalTest_ =
IF(Test[Number]<CALCULATE([Measure],ALL(Test)), "Less", "Greater")```
Regards,
Zubair

Highlighted
Helper V

## Re: Evaluate row value to measure - simple example

Thank you both @Zubair_Muhammad and @yelsherif this worked

Announcements

#### Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021