cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
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;

 

pbi.JPG 

 

 

 

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
Resolver IV
Resolver IV

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.

View solution in original post

5 REPLIES 5
Super User I
Super User I

@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


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?

Resolver IV
Resolver IV

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.

View solution in original post

@rax99

 

If you want to keep the original measure you can use

 

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


Thank you both @Zubair_Muhammad and @yelsherif this worked Smiley Happy

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors