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!
Whitewater100's avatar
Whitewater100
Icon for Solution Sage rankSolution Sage
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!