topic Re: Evaluate row value to measure - simple example in Desktop
https://community.powerbi.com/t5/Desktop/Evaluate-row-value-to-measure-simple-example/m-p/600094#M285446
<P>Thanks for that. </P><P> </P><P>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?</P>Mon, 14 Jan 2019 12:24:49 GMTrax992019-01-14T12:24:49ZEvaluate row value to measure - simple example
https://community.powerbi.com/t5/Desktop/Evaluate-row-value-to-measure-simple-example/m-p/600079#M285437
<P>Hi,</P><P> </P><P>Im trying to<SPAN> </SPAN>evaluate<SPAN> </SPAN>each column value agaisnt the measure but cant seem to figure out why it is struggling.</P><P> </P><P>A very simple 1 column table (Test);</P><P> </P><P>Number</P><P>---------</P><P>1</P><P>2</P><P>3</P><P>4</P><P>5</P><P>6</P><P>7</P><P>8</P><P>9</P><P>10</P><P> </P><P>Measure = <STRONG>STDEV.P(Test[Number])</STRONG> - which gives a Standard deviation value of <STRONG>2.87</STRONG></P><P> </P><P>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;</P><P> </P><P><STRONG>LogicalTest = IF(Test[Number]<[Measure], "Less", "Greater")</STRONG></P><P> </P><P>But the resulting table looks like this;</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-left" image-alt="pbi.JPG" style="width: 144px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/140606i3E9636693EFF0B24/image-size/large?v=1.0&px=999" role="button" title="pbi.JPG" alt="pbi.JPG" /></span> </P><P> </P><P> </P><P> </P><P>Why is it evaluating each row value as "<EM>Greater"</EM>? Surely values 1 and 2 should show "<EM>Less"</EM></P><P> </P><P>See the PBI file below:</P><P> </P><P><A href="https://www.dropbox.com/s/kyrfej1luf8uwan/MeasureVsColumn.pbix?dl=0" target="_blank">https://www.dropbox.com/s/kyrfej1luf8uwan/MeasureVsColumn.pbix?dl=0</A></P><P> </P>Mon, 14 Jan 2019 11:59:29 GMThttps://community.powerbi.com/t5/Desktop/Evaluate-row-value-to-measure-simple-example/m-p/600079#M285437rax992019-01-14T11:59:29ZRe: Evaluate row value to measure - simple example
https://community.powerbi.com/t5/Desktop/Evaluate-row-value-to-measure-simple-example/m-p/600089#M285442
<P><LI-USER uid="69371"></LI-USER></P>
<P> </P>
<P>Measures transform row context to Filter context</P>
<P><BR /><BR />So your <STRONG>STDEV.P</STRONG> gets calculated for each individual row in above formula</P>
<P> </P>
<P>Try</P>
<PRE>LogicalTest = IF(Test[Number]<STDEV.P(Test[Number]), "Less", "Greater")</PRE>
<P> </P>
<P> </P>
<P> </P>Mon, 14 Jan 2019 12:15:44 GMThttps://community.powerbi.com/t5/Desktop/Evaluate-row-value-to-measure-simple-example/m-p/600089#M285442Zubair_Muhammad2019-01-14T12:15:44ZRe: Evaluate row value to measure - simple example
https://community.powerbi.com/t5/Desktop/Evaluate-row-value-to-measure-simple-example/m-p/600094#M285446
<P>Thanks for that. </P><P> </P><P>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?</P>Mon, 14 Jan 2019 12:24:49 GMThttps://community.powerbi.com/t5/Desktop/Evaluate-row-value-to-measure-simple-example/m-p/600094#M285446rax992019-01-14T12:24:49ZRe: Evaluate row value to measure - simple example
https://community.powerbi.com/t5/Desktop/Evaluate-row-value-to-measure-simple-example/m-p/600103#M285454
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.Mon, 14 Jan 2019 12:34:33 GMThttps://community.powerbi.com/t5/Desktop/Evaluate-row-value-to-measure-simple-example/m-p/600103#M285454yelsherif2019-01-14T12:34:33ZRe: Evaluate row value to measure - simple example
https://community.powerbi.com/t5/Desktop/Evaluate-row-value-to-measure-simple-example/m-p/600107#M285458
<P><LI-USER uid="69371"></LI-USER></P>
<P> </P>
<P>If you want to keep the original measure you can use</P>
<P> </P>
<PRE>LogicalTest_ =
IF(Test[Number]<CALCULATE([Measure],ALL(Test)), "Less", "Greater")</PRE>Mon, 14 Jan 2019 12:43:39 GMThttps://community.powerbi.com/t5/Desktop/Evaluate-row-value-to-measure-simple-example/m-p/600107#M285458Zubair_Muhammad2019-01-14T12:43:39ZRe: Evaluate row value to measure - simple example
https://community.powerbi.com/t5/Desktop/Evaluate-row-value-to-measure-simple-example/m-p/600108#M285459
<P>Thank you both <LI-USER uid="42973"></LI-USER> and <LI-USER uid="102008"></LI-USER> this worked <img id="smileyhappy" class="emoticon emoticon-smileyhappy" src="https://community.powerbi.com/i/smilies/16x16_smiley-happy.png" alt="Smiley Happy" title="Smiley Happy" /></P>Mon, 14 Jan 2019 12:45:05 GMThttps://community.powerbi.com/t5/Desktop/Evaluate-row-value-to-measure-simple-example/m-p/600108#M285459rax992019-01-14T12:45:05Z