topic Moving Average -> Standard Deviation in Desktop
https://community.powerbi.com/t5/Desktop/Moving-Average-gt-Standard-Deviation/m-p/1147144#M520699
<P>Good Morning,</P><P>Reaching out to the DAX experts out there. I am trying to calculate the Standard Deviation of a Measure based on a 3 Day Moving Average (i.e. <STRONG>3DayStdDev_EquipIDRatio</STRONG>). My sample data looks like this:</P><TABLE border="1"><TBODY><TR><TD>Date</TD><TD>GSIndex</TD><TD>EquipmentIDCount</TD><TD>TotalVisits</TD><TD>EquipmentIDRatio</TD></TR><TR><TD>8/1/2019</TD><TD>1</TD><TD>3079</TD><TD>13555</TD><TD>0.227</TD></TR><TR><TD>8/2/2019</TD><TD>2</TD><TD>2931</TD><TD>13524</TD><TD>0.217</TD></TR><TR><TD>8/3/2019</TD><TD>3</TD><TD>1037</TD><TD>4997</TD><TD>0.208</TD></TR><TR><TD>8/4/2019</TD><TD>4</TD><TD>637</TD><TD>3326</TD><TD>0.192</TD></TR><TR><TD>8/5/2019</TD><TD>5</TD><TD>2326</TD><TD>8619</TD><TD>0.270</TD></TR><TR><TD>8/6/2019</TD><TD>6</TD><TD>2908</TD><TD>14599</TD><TD>0.199</TD></TR><TR><TD>8/7/2019</TD><TD>7</TD><TD>2994</TD><TD>14383</TD><TD>0.208</TD></TR></TBODY></TABLE><TABLE><TBODY><TR><TD>Date</TD><TD>GSIndex</TD><TD>3DayEquipID</TD><TD>3DayVisits</TD><TD>3DayStdDev_EquipIDRatio</TD><TD>Std. Dev Excel</TD></TR><TR><TD>8/1/2019</TD><TD>1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>8/2/2019</TD><TD>2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>8/3/2019</TD><TD>3</TD><TD>7047</TD><TD>32076</TD><TD>0</TD><TD>0.0080</TD></TR><TR><TD>8/4/2019</TD><TD>4</TD><TD>4605</TD><TD>21847</TD><TD>0</TD><TD>0.0104</TD></TR><TR><TD>8/5/2019</TD><TD>5</TD><TD>4000</TD><TD>16942</TD><TD>0</TD><TD>0.0338</TD></TR><TR><TD>8/6/2019</TD><TD>6</TD><TD>5871</TD><TD>26544</TD><TD>0</TD><TD>0.0353</TD></TR><TR><TD>8/7/2019</TD><TD>7</TD><TD>8228</TD><TD>37601</TD><TD>0</TD><TD>0.0314</TD></TR></TBODY></TABLE><P> </P><P>EquipIDCount , TotalVisits and Equipment IDRatio are all Measures.</P><P>EquipIDRatio is a measure - simply (EquipIDCount / TotalVisits).</P><P>3DayVisits and 3DayEquipID are moving average calculations wherein I use the Index to Sumx the 3 values (Current Day +2 prior days). </P><P>I am able to calculate <STRONG>Standard Deviation</STRONG> on my database as a whole using this DAX provided by <LI-USER uid="313"></LI-USER> and <LI-USER uid="82403"></LI-USER> </P><P> </P><P> </P><P> </P><LI-CODE lang="markup">StdDev_EquipIDRatio =
VAR _table = SUMMARIZE(FILTER(GeneralStatistics, GeneralStatistics[TotalVisits] <> 0),[Date],"_EquipIDRatio",[EquipmentIDRatio])
RETURN STDEVX.P(_table,[_EquipIDRatio])</LI-CODE><P> </P><P> </P><P> </P><P>The <STRONG>Standard Deviation</STRONG> in the last column was calculated in Excel. This is the number I am trying to replicate in DAX. This number is the Standard Deviation of the EquipmentIDRatio of the current day + the previous 2 days.</P><P>So on day 3, I need to take the Ratio values from Day 1 - 3</P><P>On day 4, I need to take the Ratio values from Day 2 - 4. And so on.....</P><P>Since EquipIDRatio is a measure, to calculate Std Dev., I need to construct a virtual table as I have done above, but containing only the 3 relevant values for each Date or Index. I have hit a wall and unable to figure out the correct way to do this in DAX.</P><P>Hoping one of you can point me in the right direction.</P><P>Thanks in advance and best regards,</P><P> </P>Mon, 08 Jun 2020 16:49:09 GMTrsbin2020-06-08T16:49:09ZMoving Average -> Standard Deviation
https://community.powerbi.com/t5/Desktop/Moving-Average-gt-Standard-Deviation/m-p/1147144#M520699
<P>Good Morning,</P><P>Reaching out to the DAX experts out there. I am trying to calculate the Standard Deviation of a Measure based on a 3 Day Moving Average (i.e. <STRONG>3DayStdDev_EquipIDRatio</STRONG>). My sample data looks like this:</P><TABLE border="1"><TBODY><TR><TD>Date</TD><TD>GSIndex</TD><TD>EquipmentIDCount</TD><TD>TotalVisits</TD><TD>EquipmentIDRatio</TD></TR><TR><TD>8/1/2019</TD><TD>1</TD><TD>3079</TD><TD>13555</TD><TD>0.227</TD></TR><TR><TD>8/2/2019</TD><TD>2</TD><TD>2931</TD><TD>13524</TD><TD>0.217</TD></TR><TR><TD>8/3/2019</TD><TD>3</TD><TD>1037</TD><TD>4997</TD><TD>0.208</TD></TR><TR><TD>8/4/2019</TD><TD>4</TD><TD>637</TD><TD>3326</TD><TD>0.192</TD></TR><TR><TD>8/5/2019</TD><TD>5</TD><TD>2326</TD><TD>8619</TD><TD>0.270</TD></TR><TR><TD>8/6/2019</TD><TD>6</TD><TD>2908</TD><TD>14599</TD><TD>0.199</TD></TR><TR><TD>8/7/2019</TD><TD>7</TD><TD>2994</TD><TD>14383</TD><TD>0.208</TD></TR></TBODY></TABLE><TABLE><TBODY><TR><TD>Date</TD><TD>GSIndex</TD><TD>3DayEquipID</TD><TD>3DayVisits</TD><TD>3DayStdDev_EquipIDRatio</TD><TD>Std. Dev Excel</TD></TR><TR><TD>8/1/2019</TD><TD>1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>8/2/2019</TD><TD>2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>8/3/2019</TD><TD>3</TD><TD>7047</TD><TD>32076</TD><TD>0</TD><TD>0.0080</TD></TR><TR><TD>8/4/2019</TD><TD>4</TD><TD>4605</TD><TD>21847</TD><TD>0</TD><TD>0.0104</TD></TR><TR><TD>8/5/2019</TD><TD>5</TD><TD>4000</TD><TD>16942</TD><TD>0</TD><TD>0.0338</TD></TR><TR><TD>8/6/2019</TD><TD>6</TD><TD>5871</TD><TD>26544</TD><TD>0</TD><TD>0.0353</TD></TR><TR><TD>8/7/2019</TD><TD>7</TD><TD>8228</TD><TD>37601</TD><TD>0</TD><TD>0.0314</TD></TR></TBODY></TABLE><P> </P><P>EquipIDCount , TotalVisits and Equipment IDRatio are all Measures.</P><P>EquipIDRatio is a measure - simply (EquipIDCount / TotalVisits).</P><P>3DayVisits and 3DayEquipID are moving average calculations wherein I use the Index to Sumx the 3 values (Current Day +2 prior days). </P><P>I am able to calculate <STRONG>Standard Deviation</STRONG> on my database as a whole using this DAX provided by <LI-USER uid="313"></LI-USER> and <LI-USER uid="82403"></LI-USER> </P><P> </P><P> </P><P> </P><LI-CODE lang="markup">StdDev_EquipIDRatio =
VAR _table = SUMMARIZE(FILTER(GeneralStatistics, GeneralStatistics[TotalVisits] <> 0),[Date],"_EquipIDRatio",[EquipmentIDRatio])
RETURN STDEVX.P(_table,[_EquipIDRatio])</LI-CODE><P> </P><P> </P><P> </P><P>The <STRONG>Standard Deviation</STRONG> in the last column was calculated in Excel. This is the number I am trying to replicate in DAX. This number is the Standard Deviation of the EquipmentIDRatio of the current day + the previous 2 days.</P><P>So on day 3, I need to take the Ratio values from Day 1 - 3</P><P>On day 4, I need to take the Ratio values from Day 2 - 4. And so on.....</P><P>Since EquipIDRatio is a measure, to calculate Std Dev., I need to construct a virtual table as I have done above, but containing only the 3 relevant values for each Date or Index. I have hit a wall and unable to figure out the correct way to do this in DAX.</P><P>Hoping one of you can point me in the right direction.</P><P>Thanks in advance and best regards,</P><P> </P>Mon, 08 Jun 2020 16:49:09 GMThttps://community.powerbi.com/t5/Desktop/Moving-Average-gt-Standard-Deviation/m-p/1147144#M520699rsbin2020-06-08T16:49:09ZRe: Moving Average -> Standard Deviation
https://community.powerbi.com/t5/Desktop/Moving-Average-gt-Standard-Deviation/m-p/1148028#M521121
<P>Hi <LI-USER uid="192673"></LI-USER> ,</P>
<P> </P>
<P>How about this?</P>
<LI-CODE lang="markup">3DayStdDev_EquipIDRatio =
VAR CurrentIndex =
MAX ( GeneralStatistics[GSIndex] )
VAR PreviousIndex = CurrentIndex - 2
VAR _table =
SUMMARIZE (
FILTER (
GeneralStatistics,
GeneralStatistics[TotalVisits] <> 0
&& GeneralStatistics[GSIndex] >= PreviousIndex
&& GeneralStatistics[GSIndex] <= CurrentIndex
),
[Date],
"_EquipIDRatio", [EquipmentIDRatio]
)
RETURN
STDEVX.P ( _table, [_EquipIDRatio] )
</LI-CODE>
<P> </P>
<P> </P>
<P>Best Regards,</P>
<P>Icey</P>
<P> </P>
<P>If this post <STRONG>helps</STRONG>, then please consider <EM><STRONG>Accept it as the solution</STRONG></EM> to help the other members find it more quickly.</P>Tue, 09 Jun 2020 05:22:37 GMThttps://community.powerbi.com/t5/Desktop/Moving-Average-gt-Standard-Deviation/m-p/1148028#M521121Icey2020-06-09T05:22:37ZRe: Moving Average -> Standard Deviation
https://community.powerbi.com/t5/Desktop/Moving-Average-gt-Standard-Deviation/m-p/1148910#M521520
<P>Good Morning <LI-USER uid="164362"></LI-USER> </P><P>Thanks much for the reply. I have attempted that code as as Solution, but it returns only an answer for the last date.</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="table soln 1.png" style="width: 473px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/290351i7CFA50C1CFF1CAB1/image-size/large?v=v2&px=999" role="button" title="table soln 1.png" alt="table soln 1.png" /></span><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="line chart.png" style="width: 332px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/290352iDE91A97CC3157127/image-size/large?v=v2&px=999" role="button" title="line chart.png" alt="line chart.png" /></span></P><P>The ".03142" is the correct answer for Index = 7. I too thought it would cycle through and provide an answer for each Index, but as you can from the above, it Returns just a single value.</P><P>I got up this morning thinking "CALCULATETABLE" might be the way to proceed. Really appreciate you taking the time to respond. If you have further suggestions I would be grateful to hear them. Kudos for the attempt.</P><P> </P><P>Kind Regards,</P>Tue, 09 Jun 2020 13:27:42 GMThttps://community.powerbi.com/t5/Desktop/Moving-Average-gt-Standard-Deviation/m-p/1148910#M521520rsbin2020-06-09T13:27:42ZRe: Moving Average -> Standard Deviation
https://community.powerbi.com/t5/Desktop/Moving-Average-gt-Standard-Deviation/m-p/1149688#M521876
<P>Hi <LI-USER uid="192673"></LI-USER> ,</P>
<P> </P>
<P>How about using ALLSELECTED in the expression?</P>
<LI-CODE lang="markup">3DayStdDev_EquipIDRatio =
VAR CurrentIndex =
MAX ( GeneralStatistics[GSIndex] )
VAR PreviousIndex = CurrentIndex - 2
VAR _table =
SUMMARIZE (
FILTER (
ALLSELECTED (GeneralStatistics ), ------------------edited
GeneralStatistics[TotalVisits] <> 0
&& GeneralStatistics[GSIndex] >= PreviousIndex
&& GeneralStatistics[GSIndex] <= CurrentIndex
),
[Date],
"_EquipIDRatio", [EquipmentIDRatio]
)
RETURN
STDEVX.P ( _table, [_EquipIDRatio] )</LI-CODE>
<P> </P>
<P> </P>
<P>Best Regards,</P>
<P>Icey</P>
<P> </P>
<P>If this post <STRONG>helps</STRONG>, then please consider <EM><STRONG>Accept it as the solution</STRONG></EM> to help the other members find it more quickly.</P>Wed, 10 Jun 2020 01:53:10 GMThttps://community.powerbi.com/t5/Desktop/Moving-Average-gt-Standard-Deviation/m-p/1149688#M521876Icey2020-06-10T01:53:10ZRe: Moving Average -> Standard Deviation
https://community.powerbi.com/t5/Desktop/Moving-Average-gt-Standard-Deviation/m-p/1150836#M522292
<P>Good Morning <LI-USER uid="164362"></LI-USER> </P><P> </P><P>Yes, the edit works. I finally am able to get the Std. Dev values for each Index. Thank you very much for the assistance.</P><P> </P><P>Unfortunately, still having some issues having it display properly in my line charts. I have to figure out why it is behaving like that.</P><P>But thanks again, it is a relief to finally solve the first part of this.</P><P> </P><P>Kind Regards and all the best,</P>Wed, 10 Jun 2020 12:59:09 GMThttps://community.powerbi.com/t5/Desktop/Moving-Average-gt-Standard-Deviation/m-p/1150836#M522292rsbin2020-06-10T12:59:09Z