- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Evaluate row value to measure - simple example

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

rax99

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-14-2019
03:56 AM

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

Solved! Go to Solution.

Report Inappropriate Content

Message 1 of 6

1 ACCEPTED SOLUTION

Accepted Solutions

yelsherif

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-14-2019
04:34 AM

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

Zubair_Muhammad

Super User

Re: Evaluate row value to measure - simple example

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-14-2019
04:15 AM

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

rax99

Member

Re: Evaluate row value to measure - simple example

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-14-2019
04:24 AM

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?

yelsherif

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-14-2019
04:34 AM

Zubair_Muhammad

Super User

Re: Evaluate row value to measure - simple example

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-14-2019
04:43 AM

If you want to keep the original measure you can use

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

rax99

Member

Re: Evaluate row value to measure - simple example

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-14-2019
04:45 AM

Thank you both @Zubair_Muhammad and @yelsherif this worked