Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Team,
I've to calculate Standard Deviation for Rolling 3 months average sales. Unfortunately, I created the average as a quick measure. I'm looking for suggestions to calculate Standard Deviation for the same.
Thanks,
Siva
If you created the average as a quick measure, it should give you the formula still.
Check out my post on Primary and Secondary School Performance for Ohio. It includes the PBIX file and the calculation of standard deviation was central to the project:
Should be able to download the PBIX and see how I did it.
Looks like I just used STDEV.P function
Overall Score District StdDev = STDEV.P(DISTRICT[OVERALL SCORE])
https://msdn.microsoft.com/en-us/library/gg492207.aspx
Thank you!
The challenge here is I need to find standard deviation for a measure which is a quick measure to calculate 3 months rolling average.
I think the solution would be creating it as a calculated measure. Is it possible?
Ah, it sounds like you are trying to do an aggregation. The trick there is to use SUMMARIZE. See this article:
https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
A quick measure still creates a real measure, it just does it quickly without you having to hand-type the DAX code.
I'm Sorry. I think I didn't make it clear.
My problem is, STDEV.P expects column to calculate. But I have it as a measure which I need to find Std Dev.
Let's say,
I wanted to calculate Standard deviation for rolling month average. But it is actually a measure. So I'm unable to use it in STDEV.P().
Is there a way to calculate rolling N month average as a calculated column?
Kindly help me!
Right, so that article shows the use of SUMMARIZE, so you put your grouping and your measure into the SUMMARIZE. This essentially turns the measure into a column in the context of the table coming back from SUMMARIZE and then you can use whatever aggregation on that. In your case you would want to use the STDEVX.P function. Note the "X".
I tried the same. Unfortunately, I got the following warning,
A circular dependency was detected
@SivaMani,
Please take a look at the following blog and similar thread about how to calculate standard deviation of a measure.
Standard Deviation Demystified in Power Pivot
Calculating standard deviation of the calculated measure
Regards,
Lydia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |