topic calculate average per category per year in Desktop
https://community.powerbi.com/t5/Desktop/calculate-average-per-category-per-year/m-p/747778#M360431
<P>hello,</P><P> </P><P>New to power bi here and would appreciate some help. </P><P>I want to calculate the average of units per year for apo key and material key (which make a distinct category) for my data. </P><P>For certain purposes the average needs to be calculated for previous year, current year and next year which are denoted by 'PY', 'CY' and 'NY' in a column marked 'IS YEAR'. </P><P> </P><P>the column highlighted is what i need to obtain. </P><P> </P><P>I have tried using this- </P><DIV><DIV><SPAN>Avg = </SPAN></DIV><DIV><SPAN>CALCULATE (</SPAN></DIV><DIV><SPAN>AVERAGE (z[units] ),</SPAN></DIV><DIV><SPAN>FILTER (z, z[material key]=EARLIER(z[material key]))</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV><SPAN>which is calculating the average based on apo key and material key but the average does not change for different years. </SPAN></DIV><DIV> </DIV><DIV><SPAN>can someone provide help with this.</SPAN></DIV><DIV><SPAN>Thanks <span class="lia-unicode-emoji" title=":slightly_smiling_face:">ðŸ™‚</span><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="sample.PNG" style="width: 461px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/179904iC6258DE45E42A3D7/image-size/large?v=v2&px=999" role="button" title="sample.PNG" alt="sample.PNG" /></span></SPAN></DIV></DIV>Wed, 24 Jul 2019 11:24:27 GMTAnonymous2019-07-24T11:24:27Zcalculate average per category per year
https://community.powerbi.com/t5/Desktop/calculate-average-per-category-per-year/m-p/747778#M360431
<P>hello,</P><P> </P><P>New to power bi here and would appreciate some help. </P><P>I want to calculate the average of units per year for apo key and material key (which make a distinct category) for my data. </P><P>For certain purposes the average needs to be calculated for previous year, current year and next year which are denoted by 'PY', 'CY' and 'NY' in a column marked 'IS YEAR'. </P><P> </P><P>the column highlighted is what i need to obtain. </P><P> </P><P>I have tried using this- </P><DIV><DIV><SPAN>Avg = </SPAN></DIV><DIV><SPAN>CALCULATE (</SPAN></DIV><DIV><SPAN>AVERAGE (z[units] ),</SPAN></DIV><DIV><SPAN>FILTER (z, z[material key]=EARLIER(z[material key]))</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV><SPAN>which is calculating the average based on apo key and material key but the average does not change for different years. </SPAN></DIV><DIV> </DIV><DIV><SPAN>can someone provide help with this.</SPAN></DIV><DIV><SPAN>Thanks <span class="lia-unicode-emoji" title=":slightly_smiling_face:">ðŸ™‚</span><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="sample.PNG" style="width: 461px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/179904iC6258DE45E42A3D7/image-size/large?v=v2&px=999" role="button" title="sample.PNG" alt="sample.PNG" /></span></SPAN></DIV></DIV>Wed, 24 Jul 2019 11:24:27 GMThttps://community.powerbi.com/t5/Desktop/calculate-average-per-category-per-year/m-p/747778#M360431Anonymous2019-07-24T11:24:27ZRe: calculate average per category per year
https://community.powerbi.com/t5/Desktop/calculate-average-per-category-per-year/m-p/747799#M360439
<P>You're nearly there. You just have to add in the other conditions (which you have described) in to the filter.</P><P>e.g. && z[apo key] = EARLIER (z[apo key])</P><P> </P><P>and the same for year.</P><P> </P><P>Edit: the CY, NY and PY are irrelevant because you have the Year pre-calculated . Can you confirm?</P>Wed, 24 Jul 2019 11:50:18 GMThttps://community.powerbi.com/t5/Desktop/calculate-average-per-category-per-year/m-p/747799#M360439HotChilli2019-07-24T11:50:18ZRe: calculate average per category per year
https://community.powerbi.com/t5/Desktop/calculate-average-per-category-per-year/m-p/747801#M360440
<P>Yep that works <span class="lia-unicode-emoji" title=":slightly_smiling_face:">ðŸ™‚</span> <BR /><BR /></P><P>i really dont understand how i missed this small step. </P><P>Looks like i need to take a break.</P><P>But thanks a tonne</P>Wed, 24 Jul 2019 11:51:16 GMThttps://community.powerbi.com/t5/Desktop/calculate-average-per-category-per-year/m-p/747801#M360440Anonymous2019-07-24T11:51:16ZRe: calculate average per category per year
https://community.powerbi.com/t5/Desktop/calculate-average-per-category-per-year/m-p/748787#M360917
<P>hey! </P><P>the calculation of the average went well. but now i'm stuck at calculating standard deviation of units per category (apo key+ material key) per year which is PY,CY and NY</P><P> </P><P>I understand simply using the stdev.p wouldnt work as it computes based on the formula SUM(X-Xavg)^2/n and n keeps changing per category per year.</P><P>is using stdevx.p a better apporach? </P><P>if so, then what would be the expression for it so that standard deviation is obtained per category per year?</P><P> </P><P>thank you <span class="lia-unicode-emoji" title=":slightly_smiling_face:">ðŸ™‚</span></P>Thu, 25 Jul 2019 08:23:24 GMThttps://community.powerbi.com/t5/Desktop/calculate-average-per-category-per-year/m-p/748787#M360917Anonymous2019-07-25T08:23:24Z