Forum Discussion
Standard Deviation from 4 measured columns.... DARN!
Hi Demdej
I have just realised that you wish to measure the standard deviation at a yearly granularity, not at the granularity of the data within. So over 5 data points
Ie the calculation would look like this if we were to draw it out:
SQRT( ( (2016prod-avg)²+(2017prod-avg)²+....+(2020prod-avg)²)/5 )
What we need for this are the "aggregateX" functions. These are probably easier to understand if you research "sumX" and "averageX" as they are better documented & used.
Essentially, these functions will allow you to iterate across rows that you define:
eg
AverageX( Values([customerID]), sum(Sales)) will group by customer, calculate the sales for each then work out the average of all of those results. Giving you the average sales per customer.
In your example we are going to use
Year STDEV = STDEVX.P( VALUES(APY[Crop_Year]), [x years production])Where
x years production = CALCULATE(sum(APY[Production]),FILTER(APY,APY[Crop_Year]>= 2016))Plugging this into the table gives the result as manually calculated in excel.
Result in PBI:
calculation in Excel:
Pi
Thanks so much for the thorough explanation and examples. They are always so helpful.
The fact that we can't use a pivot table to illustrate the st.dev is difficult because, like you, I created a measure for each year, but I still could not get an accurate SD.
For some reason, it's the same for you:
I just double check my data in Excel, because I'm far better at Excel than PowerBI. Since the column thing is such an issue, I decided to put my dates all in one column, but I STILL can't get an accurate count.
I'm truly stomped. 😞
Resolver IIHi Demdej
I have just realised that you wish to measure the standard deviation at a yearly granularity, not at the granularity of the data within. So over 5 data points
Ie the calculation would look like this if we were to draw it out:
SQRT( ( (2016prod-avg)²+(2017prod-avg)²+....+(2020prod-avg)²)/5 )
What we need for this are the "aggregateX" functions. These are probably easier to understand if you research "sumX" and "averageX" as they are better documented & used.
Essentially, these functions will allow you to iterate across rows that you define:
eg
AverageX( Values([customerID]), sum(Sales)) will group by customer, calculate the sales for each then work out the average of all of those results. Giving you the average sales per customer.
In your example we are going to use
Year STDEV = STDEVX.P( VALUES(APY[Crop_Year]), [x years production])Where
x years production = CALCULATE(sum(APY[Production]),FILTER(APY,APY[Crop_Year]>= 2016))
Plugging this into the table gives the result as manually calculated in excel.
Result in PBI:
calculation in Excel:
Pi