Forum Discussion

a_hawk's avatar
a_hawk
Frequent Visitor
4 years ago
Solved

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 ...
Control Chart
Control charts
DAX
Dax commands help
I-MR
IMR
Moving Range
  • Whitewater100's avatar
    Whitewater100
    4 years ago

    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])
    return
    AVERAGEX(_mytable,
    ABS([ratio] - [Ratio Last Month])
    )
     
    I beleive the same approach will handle Standard Dev.
     
    I hope this helps!
a_hawk's avatar
a_hawk
Frequent Visitor
4 years ago

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.

Whitewater100's avatar
Whitewater100
Icon for Solution Sage rankSolution Sage
4 years ago

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])
return
AVERAGEX(_mytable,
ABS([ratio] - [Ratio Last Month])
)
 
I dropped it into your table and it worked. Sorry about that!