Forum Discussion
DAX: Moving Range Average & Standard Deviation
Hello.
I am trying to build I-MR charts on the month level and am having difficultly getting the average & standard deviation of my "Moving Range" measure.
Here is a sample of the excel data I start with. The "Date" column is a calculated column created in Power BI after the data is loaded:
| Month | Year | metric_a | metric_b | Date |
| 1 | 2020 | 5.5 | 1/1/2020 12:00:00 AM | |
| 1 | 2020 | 4.5 | 1/1/2020 12:00:00 AM | |
| 1 | 2020 | 6.2 | 1/1/2020 12:00:00 AM | |
| 2 | 2020 | 3.3 | 2/1/2020 12:00:00 AM | |
| 2 | 2020 | 6.7 | 2/1/2020 12:00:00 AM | |
| 2 | 2020 | 5.4 | 2/1/2020 12:00:00 AM | |
| 3 | 2020 | 2.2 | 3/1/2020 12:00:00 AM | |
| 3 | 2020 | 10.8 | 3/1/2020 12:00:00 AM | |
| 3 | 2020 | 7.8 | 3/1/2020 12:00:00 AM |
The measure I am using for the I-MR charts is a ratio between the two metrics. The DAX formulas I used to create the ratio measures are:
Sum_A = SUM(Sheet1[metric_a])
Sum_B = SUM(Sheet1[metric_b])
Ratio = DIVIDE([Sum_A], [Sum_B])
I used the PREVIOUSMONTH() function to calculate the ratio value from the previous month and then subtract the two measures to get Moving Range:
Ratio Last Month = CALCULATE('Measures Table'[Ratio], PREVIOUSMONTH('Date Table'[Date]))
Moving Range = ABS([Ratio] - [Ratio Last Month])
Viewing a table of the results everything looks accurate:
| Year | Month | Ratio | Ratio Last Month | Moving Range |
| 2020 | January | 62.00% | 62.00% | |
| 2020 | February | 54.00% | 62.00% | 8.00% |
| 2020 | March | 60.00% | 54.00% | 6.00% |
| 2020 | April | 51.90% | 60.00% | 8.10% |
To build the LCL and UCL lines for the I-MR charts I need the average and standard deviation of both "Ratio" and "Moving Range". I attempted to calculate these metrics with these measures:
Ratio Avg = AverageX(ALLSELECTED('Date Table'[Date]),[Ratio])
Ratio StdDev = STDEVX.P('Date Table',CALCULATE([Ratio]))
Moving Range Avg = AverageX(ALLSELECTED('Date Table'[Date]),[Moving Range])
Moving Range StdDev = STDEVX.P('Date Table',CALCULATE([Moving Range]))
I placed the average and standard deviation measures in a card. The average and standard deviation are correct for the Ratio measure but incorrect for the Moving Range measure:
According to Excel the average Moving Range is 8.88% with a standard deviation of 11.62%. Any idea what I need to change to get the correct results for Moving Range? Any help is greatly appreciated, thank you!
Link to .pbix: https://drive.google.com/file/d/1CWtFiHCjNhANGD9XCwvyIo8dvaeerry-/view?usp=sharing
Hello:
You can try this for your answer of 8.8%.
Avg Range =VAR _mytable = SUMMARIZECOLUMNS('Date Table'[Month],'Date Table'[Year],"ratio", [Ratio],"LastMonthRatio", [Ratio Last Month])returnAVERAGEX(_mytable,ABS([ratio] - [Ratio Last Month]))I beleive the same approach will handle Standard Dev.I hope this helps!
3 Replies
Hello:
You can try this for your answer of 8.8%.
Avg Range =VAR _mytable = SUMMARIZECOLUMNS('Date Table'[Month],'Date Table'[Year],"ratio", [Ratio],"LastMonthRatio", [Ratio Last Month])returnAVERAGEX(_mytable,ABS([ratio] - [Ratio Last Month]))I beleive the same approach will handle Standard Dev.I hope this helps!Hello,
Thanks for your help. Using that DAX allowed me to correctly calculate Average and Standard Deviation for the Moving Range.
I placed those measures on a visual and tried to use slicers with the visual but I got the following error:
Calculation error in measure 'Measures Table'[Avg Range]: SummarizeColumns() and AddMissingItems() may not be used in this context.
This error appears whenever I try to use more than 1 slicer at once to filter the visuals. Any ideas for a workaround?
Thank you so much for your help so far.
Hi Hawk:
I think SummarizeColumns isn't fully supported so if you try this instead, it should work:
Avg Range2 =VAR _mytable = SUMMARIZE('Date Table','Date Table'[Month],'Date Table'[Year],"ratio", [Ratio],"LastMonthRatio", [Ratio Last Month])returnAVERAGEX(_mytable,ABS([ratio] - [Ratio Last Month]))I dropped it into your table and it worked. Sorry about that!
