Forum Discussion
STD
Anonymous,
Honestly I don't know much about STDEV functions and that's partially the reason why I stopped to read your post. I decided to try and play with some numbers making measures and whatnot and tried to visualize as you have.
After reading https://powerpivotpro.com/2013/10/standard-deviation-demystified-in-power-pivot/, near the bottom it states, "Important: Average and STD DEV Must Have the Same “Granularity”, possibly the reason for your visualization being incorrect is because in the filter context the same grainularity is not maintained.
You probably already realized this and that is the reason for your post is to have help with a measure to enforce the granularity.
MIght I suggest providing data with the expected values?
I look forward to the responses and solution.
I am not sure what Granularity is, but I will go read your link and maybe I will find out. ( I am fairly new to dax)
Allthough the result I posted is correct, it s not dynamic, I can make the average dynamic, but since the std did not work I opted to go for the average over the FY since that is the way it was done in excel. I achieved this through a measure
AVERAGE = CALCULATE(AVERAGE(Column), ALL(Dates), VALUES(Dates[FY])), I then wrote this measure to a column in my table, then another column which calculated the variance and so on and so forth.
Here is the example of what I have and what I wanto achieve, but dynamically so that if I select month, or week, I will see my average, UL, LL for that particular month or week. Well, mine is dynamic in the sense that in changes form fy to fy, but stays static for smaller periods. First Column is my data values, others are all calculated columns
Ok so I have been using the function STDEV = CALCULATE(STDEV.S(Column), ALLSELECTED(Dates)) and what I have seen now while playing around, that the function is actually very accurate, it is only for the FY18. Now it might be that my data set is already set to 31 august, so it use 365 days as N, but then that would also be the case for my average yet my average function works perfect, I mean allselected means allselected right?