topic STDEV DAX when using row with value 1 in DAX Commands and Tips
https://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2539541#M71494
<P>I am trying to get the standard deviation to develop control charts. I was able to calculate the SUM and AVG using the below formulas and both work perfectly. The problem is my table is using a column called Number Of Issues Reported which always has a value of 1 in it. This is making it difficult to produce the Standard Deviation because it is coming up 0 because all the values are 1 in the column. Is there a way to get it to calculate the Standard Deviation properly similiar to what I did below for SUM and AVG? I would prefer this to changing the SQL query since everything else works. Below are the working formulas and the table layout. </P><P> </P><DIV><DIV><SPAN>SUM = CALCULATE(SUMX(Table1, Table1[Number of Issues Reported]),</SPAN></DIV><DIV><SPAN>ALLSELECTED(Table1[Month]))</SPAN></DIV><DIV> </DIV><DIV><DIV><SPAN>AVG = CALCULATE(SUMX(Table1, Table1[Number of Issues Reported])/DISTINCTCOUNT(Table1[Month]),</SPAN></DIV><DIV><SPAN>ALLSELECTED(Table1[Month]))</SPAN></DIV><DIV> </DIV><DIV><DIV><SPAN>STDEV = CALCULATE(STDEVX.S(Table1, Table1[Number of Issues Reported]),</SPAN></DIV><DIV><SPAN>ALLSELECTED(FACTS[Month])) </SPAN></DIV><DIV><SPAN><STRONG>NOT WORKING and I understand why but dont know how to tweak. It is taking that 1 value and giving me a 0 StDev because all the rows have that 1. </STRONG></SPAN></DIV><DIV> </DIV><DIV><SPAN><STRONG>Sample table layout</STRONG></SPAN></DIV><DIV><TABLE><TBODY><TR><TD>Month</TD><TD>Major</TD><TD>Number of Issues Reported</TD></TR><TR><TD>2020-1</TD><TD>AAA</TD><TD>1</TD></TR><TR><TD>2021-1</TD><TD>BBB</TD><TD>1</TD></TR><TR><TD>2019-2</TD><TD>CCC</TD><TD>1</TD></TR><TR><TD>2018-5</TD><TD>AAA</TD><TD>1</TD></TR><TR><TD>2020-2</TD><TD>BBB</TD><TD>1</TD></TR><TR><TD>2021-3</TD><TD>CCC</TD><TD>1</TD></TR><TR><TD>2022-3</TD><TD>AAA</TD><TD>1</TD></TR><TR><TD>2019-4</TD><TD>BBB</TD><TD>1</TD></TR><TR><TD>2018</TD><TD>CCC</TD><TD>1</TD></TR></TBODY></TABLE></DIV><DIV> </DIV></DIV></DIV></DIV>Wed, 25 May 2022 16:25:17 GMTcommon7632022-05-25T16:25:17ZSTDEV DAX when using row with value 1
https://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2539541#M71494
<P>I am trying to get the standard deviation to develop control charts. I was able to calculate the SUM and AVG using the below formulas and both work perfectly. The problem is my table is using a column called Number Of Issues Reported which always has a value of 1 in it. This is making it difficult to produce the Standard Deviation because it is coming up 0 because all the values are 1 in the column. Is there a way to get it to calculate the Standard Deviation properly similiar to what I did below for SUM and AVG? I would prefer this to changing the SQL query since everything else works. Below are the working formulas and the table layout. </P><P> </P><DIV><DIV><SPAN>SUM = CALCULATE(SUMX(Table1, Table1[Number of Issues Reported]),</SPAN></DIV><DIV><SPAN>ALLSELECTED(Table1[Month]))</SPAN></DIV><DIV> </DIV><DIV><DIV><SPAN>AVG = CALCULATE(SUMX(Table1, Table1[Number of Issues Reported])/DISTINCTCOUNT(Table1[Month]),</SPAN></DIV><DIV><SPAN>ALLSELECTED(Table1[Month]))</SPAN></DIV><DIV> </DIV><DIV><DIV><SPAN>STDEV = CALCULATE(STDEVX.S(Table1, Table1[Number of Issues Reported]),</SPAN></DIV><DIV><SPAN>ALLSELECTED(FACTS[Month])) </SPAN></DIV><DIV><SPAN><STRONG>NOT WORKING and I understand why but dont know how to tweak. It is taking that 1 value and giving me a 0 StDev because all the rows have that 1. </STRONG></SPAN></DIV><DIV> </DIV><DIV><SPAN><STRONG>Sample table layout</STRONG></SPAN></DIV><DIV><TABLE><TBODY><TR><TD>Month</TD><TD>Major</TD><TD>Number of Issues Reported</TD></TR><TR><TD>2020-1</TD><TD>AAA</TD><TD>1</TD></TR><TR><TD>2021-1</TD><TD>BBB</TD><TD>1</TD></TR><TR><TD>2019-2</TD><TD>CCC</TD><TD>1</TD></TR><TR><TD>2018-5</TD><TD>AAA</TD><TD>1</TD></TR><TR><TD>2020-2</TD><TD>BBB</TD><TD>1</TD></TR><TR><TD>2021-3</TD><TD>CCC</TD><TD>1</TD></TR><TR><TD>2022-3</TD><TD>AAA</TD><TD>1</TD></TR><TR><TD>2019-4</TD><TD>BBB</TD><TD>1</TD></TR><TR><TD>2018</TD><TD>CCC</TD><TD>1</TD></TR></TBODY></TABLE></DIV><DIV> </DIV></DIV></DIV></DIV>Wed, 25 May 2022 16:25:17 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2539541#M71494common7632022-05-25T16:25:17ZRe: STDEV DAX when using row with value 1
https://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2539933#M71520
<P>Hi:</P><P>You could make a summary table and then do STD.P on a column after the results have been aggregated. But do you want STD on number of observations or maybe some other column. Your STD will be more useful if everything is always the same.</P><P>New Table:</P><P>Summary_Tble =ADDCOLUMNS(</P><P> SUMMARIZE(<SPAN>Table1, Table1[Month], Table1[Major]),</SPAN></P><P><SPAN> "No. Issues", SUM(Table1[Number of Issues Reported] )</SPAN></P><P> </P><P><SPAN>Issues STD = = STDEV.P(Summary_Tble,Summary_Tble[No. Issues])<BR /> </SPAN></P><P><SPAN>It could help you to have a Date Table all set up and record your fact table entries by date.</SPAN></P><P> </P><P><SPAN>* Note on STD below:</SPAN></P><P><SPAN>STDEV.P assumes that the column refers to the entire population. If your data represents a sample of the population, then compute the standard deviation by using STDEV.S</SPAN></P><P> </P><P><SPAN>I hope I have answered your question. Thanks..</SPAN></P><P><SPAN> </SPAN></P>Wed, 25 May 2022 20:48:35 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2539933#M71520Whitewater1002022-05-25T20:48:35ZRe: STDEV DAX when using row with value 1
https://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2540079#M71527
<P>Thanks for the response. I will try your solution and respond in the am. STDEV on Issues and have the STDEV calculate based off the end-users date selection. My average and Sum works this way but see why the STDEV doesnt. Again, will try what you suggested tomorrow morning. Thanks again. </P>Thu, 26 May 2022 00:22:29 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2540079#M71527common7632022-05-26T00:22:29ZRe: STDEV DAX when using row with value 1
https://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2541412#M71584
<P>I am trying your code out now and getting that dreaded "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." Assuming it is something in my setup of the table? I have this linked from a SQL database and it is a stored procedure connected to multiple tables. Probably not why this is happening but unsure. I mirrored exactly what you had written so it has to be something on my end. </P>Thu, 26 May 2022 12:18:11 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2541412#M71584common7632022-05-26T12:18:11ZRe: STDEV DAX when using row with value 1
https://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2541492#M71590
<P>Hi:</P><P>If the number of issues is always one/event than there can't be any STD becasuse everything is always one. Are you trying to get STD on monthly or weekly issues? Normally the STD would work something like this:</P><P>Test 1 - result 40</P><P>Test 2 - result 50</P><P>Test 3 - result 42</P><P>Sum = 132</P><P>Avg = 44</P><P>STD(pop) = 4.32</P><P> </P><P>But when each result is one the STD = 0.</P><P> </P><P>I hope my reply makes sense to you.</P><P> </P><P>Thank you,</P><P> </P>Thu, 26 May 2022 13:00:15 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2541492#M71590Whitewater1002022-05-26T13:00:15ZRe: STDEV DAX when using row with value 1
https://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2541499#M71591
<P>It does. What I was hoping was to get the standard deviation from the sum of each month selected in the slicer. So for instance if the end-user selects the below:</P><P>1-2020 with 1000</P><P>2-2020 with 2000</P><P>3-2020 with 1500</P><P>Then in simple form calculate the standard deviation of those three which would be STDEV(1000, 2000, 1500)</P>Thu, 26 May 2022 13:04:37 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2541499#M71591common7632022-05-26T13:04:37ZRe: STDEV DAX when using row with value 1
https://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2541519#M71594
<P>Hi:</P><P>Maybe your sample data isn't matching your latest example. I see 1 for Jan 2020 but you mention it is 1000? Do you have sample data to share? Just something to represent this issue so I can see those different totals. Thanks</P>Thu, 26 May 2022 13:18:40 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2541519#M71594Whitewater1002022-05-26T13:18:40ZRe: STDEV DAX when using row with value 1
https://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2541568#M71598
<P>Yes Sorry. I am doing a poor job at explaining. Below is a sample of the data layout and a quick pivot of what I want it to do. </P><P> </P><P>LAYOUT SAMPLE</P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>MONTH</TD><TD>MAJOR</TD><TD>ISSUES</TD></TR><TR><TD>2022-2</TD><TD>AAA</TD><TD>1</TD></TR><TR><TD>2022-2</TD><TD>BBB</TD><TD>1</TD></TR><TR><TD>2022-3</TD><TD>AAA</TD><TD>1</TD></TR><TR><TD>2022-3</TD><TD>BBB</TD><TD>1</TD></TR><TR><TD>2022-4</TD><TD>AAA</TD><TD>1</TD></TR><TR><TD>2022-4</TD><TD>BBB</TD><TD>1</TD></TR><TR><TD>2022-5</TD><TD>AAA</TD><TD>1</TD></TR><TR><TD>2022-5</TD><TD>BBB</TD><TD>1</TD></TR><TR><TD>2022-2</TD><TD>AAA</TD><TD>1</TD></TR><TR><TD>2022-2</TD><TD>BBB</TD><TD>1</TD></TR><TR><TD>2022-3</TD><TD>AAA</TD><TD>1</TD></TR></TBODY></TABLE><P> </P><P> </P><P>LAYOUT AND CALC I need that Standard Dev to create Upper and Lower Control Limits for the chart. </P><P> </P><TABLE><TBODY><TR><TD>MAJOR</TD><TD>2022-2</TD><TD>2022-3</TD><TD>2022-4</TD><TD>2022-5</TD><TD>Grand Total</TD></TR><TR><TD>AAA</TD><TD>2</TD><TD>5</TD><TD>5</TD><TD>7</TD><TD>19</TD></TR><TR><TD>BBB</TD><TD>3</TD><TD>5</TD><TD>6</TD><TD>5</TD><TD>19</TD></TR><TR><TD>Grand Total</TD><TD>5</TD><TD>10</TD><TD>11</TD><TD>12</TD><TD>38</TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>STDEVIATION</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>AAA</TD><TD>1.26</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>BBB</TD><TD>1.89</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE><P> </P>Thu, 26 May 2022 13:39:26 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2541568#M71598common7632022-05-26T13:39:26ZRe: STDEV DAX when using row with value 1
https://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2541681#M71604
<P>Hi:</P><P>Please see file. You willl notice I have summary table in and have set up a date table. I made up the data points but results appear to work as intended. What do you think?</P><P><A title="STD on Month Totals" href="https://drive.google.com/file/d/1ibEFS6MXsidsA3ny7_KnDY-UAnJ0UOde/view?usp=sharing" target="_self">https://drive.google.com/file/d/1ibEFS6MXsidsA3ny7_KnDY-UAnJ0UOde/view?usp=sharing</A> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Whitewater100_0-1653576278798.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/722135iAA25E08DE4BCCDAB/image-size/medium?v=v2&px=400" role="button" title="Whitewater100_0-1653576278798.png" alt="Whitewater100_0-1653576278798.png" /></span></P><P> </P>Thu, 26 May 2022 14:44:46 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2541681#M71604Whitewater1002022-05-26T14:44:46ZRe: STDEV DAX when using row with value 1
https://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2541835#M71612
<P>That would work perfectly if I wasnt trying to insert the STDEV into every column of month that the end-user selects. I need that value in each column for month to develop the UCL and LCL. I went in an rewrote the query and it looks like it now works ok so I can close this out. Basically instead of having each row represent 1, I rewrote the query in SQL to group the totals. I then was able to calculate the STDEV properly and then the UCL and LCL to get this functioning. Thanks so much for taking the time to help me with this. </P>Thu, 26 May 2022 16:06:21 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2541835#M71612common7632022-05-26T16:06:21ZRe: STDEV DAX when using row with value 1
https://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2541854#M71614
<P>I'm happy it is working for you with your extra touch! You are welcome! </P>Thu, 26 May 2022 16:12:30 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/STDEV-DAX-when-using-row-with-value-1/m-p/2541854#M71614Whitewater1002022-05-26T16:12:30Z