Moving Range calculation for I-MR control chart

[peter789]

As the next step of my control chart project I realise I need to calculate the average of the Moving Range of a group of values in my Pivot Table.
Wikipedia defines the calculation formula here:

https://en.wikipedia.org/wiki/Shewhart_individuals_control_chart

As a first step I have created an Index column in my data using Power Query. Next I tried to test the use of EARLIER to find the minimum of one value and its neighbour in the next row. However this is where it gets stuck.
Any help with the measure I am using would be appreciated please or advice as to whether I should be taking a different tack. I have been reluctant to try and create calculated columns in my data source as just even adding the index column has increased the file size by 20%.
The measure I've tried is:

=CALCULATE(MIN(QryAnalysis[Value]),FILTER(QryAnalysis,QryAnalysis[Oxide]=EARLIER(QryAnalysis[Oxide]) && QryAnalysis[Index]=EARLIER((QryAnalysis[Index]) && QryAnalysis[Index]=EARLIER(QryAnalysis[Index])+1)))

The error is:
"This formula is invalid or incomplete: 'Calculation error in measure 'QryAnalysis'[Minimum of two values]: EARLIER/EARLIEST refers to an earlier row context which doesn't exist.'. "

My Pivot Table looks like:
[TABLE="width: 288"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Oxide[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Na2O %[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Average Value[/TD]
[/TR]
[TR]
[TD]Sample Date[/TD]
[TD]Index[/TD]
[TD]Amber[/TD]
[/TR]
[TR]
[TD="align: right"]03/04/2017
[/TD]
[TD="align: right"]132950[/TD]
[TD="align: right"]11.97[/TD]
[/TR]
[TR]
[TD="align: right"]10/04/2017
[/TD]
[TD="align: right"]132951[/TD]
[TD="align: right"]12.00[/TD]
[/TR]
[TR]
[TD="align: right"]18/04/2017[/TD]
[TD="align: right"]132952[/TD]
[TD="align: right"]11.96[/TD]
[/TR]
[TR]
[TD="align: right"]24/04/2017[/TD]
[TD="align: right"]132953[/TD]
[TD="align: right"]12.09[/TD]
[/TR]
[TR]
[TD="align: right"]02/05/2017[/TD]
[TD="align: right"]132954[/TD]
[TD="align: right"]12.11[/TD]
[/TR]
[TR]
[TD="align: right"]08/05/2017[/TD]
[TD="align: right"]132955[/TD]
[TD="align: right"]12.07[/TD]
[/TR]
[TR]
[TD="align: right"]15/05/2017[/TD]
[TD="align: right"]132956[/TD]
[TD="align: right"]12.08[/TD]
[/TR]
[TR]
[TD="align: right"]22/05/2017
[/TD]
[TD="align: right"]132957[/TD]
[TD="align: right"]12.08
[/TD]
[/TR]
[TR]
[TD="align: right"]29/05/2017
[/TD]
[TD="align: right"]132958[/TD]
[TD="align: right"]11.98[/TD]
[/TR]
</tbody>[/TABLE]

Should I plough on? My next measure would be to work out the Max, then AVERAGEX MAX- MIN.

 

[gazpage]

Yeah, that won't work.

Using a measure in another DAX formula puts an implicit CALCULATE around it, and CALCULATE converts an existing row context into a filter context. In your formula that means [Starting Index] is calculated in the context of the current row if the MINX iteration. The only way around this is to breakout the measure in full

I think you need to remove the CALCULATE in [Starting Index] (not sure why it's there, but it will cause the same problem as above) and then replace [Starting Index] in the second measure with the full formula broken out.

 

[peter789]

I'm not sure how to take the CALCULATE out as the MIN calculate needs to operate with the ALLSELECTED [sample date] to calculate the lowest index value of each group. Without it I think the MIN operates on each row and is effectively redundant as it just returns the original value of Index?

 

[gazpage]

Who boy! This is a doozy!

Not sure I understand well enough your calculation, so sorry if I give bad advice. The below is a bit of a shot gun approach of the things I would try if I was the one working in your data. I hope this is more helpful than not doing anything. I am finding it hard to work out how to break out of the row context that is transitioned into a filter context bu CALCULATE.

First to check that you are not using Excel 2016 or Power BI desktop. If you are then we should make Starting Index a variable and I think it would work then.

Second, try putting the all the other columns in the that you don't need to apply to work out the minimum in the ALLSELECTED. e.g ALLSELECTED(QryAnalysis[sample date], QryAnalysis[INDEX] etc). Only leaving teh Colour Group and any other items that are required to pin down the context to evaluate the minimum.

Finally, maybe we need to rebuild the evaluation context entirely for Starting Index so it is something like.

CALCULATE ( 
    MIN ( QryAnalysis[Index] ),
    FILTER ( 
        ALLSELECTED ( QryAnalysis ),
        QryAnalysis[ColourGroup] = EARLIEST (QryAnalysis[ColourGroup],
        OTHER CONDITIONS THAT NEED TO BE APPLIED TO WORK OUT THE MIN INDEX
    )
)(

My logic is that by using ALLSELECTED you filter the entire table, ignoring any existing filter contexts other than the SELECTED ones. Then pick up all the rows you need and take the min of the resulting table. I've never used EARLIEST before, but I figure you need to break out of both the evaluation context in my FILTER iteration, and also in the MINX row context.

Sorry not to be more help.

 

[peter789]

Thanks again and no need to apologise, you've helped me move much further forward than I would have managed with weeks of stumbling around, I really appreciate the time you've spent so far with my esoteric problem (might be why I've not seen Moving Range covered in any of the statistical calculations in DAX I've googled)! You've given me plenty to work on. (By the way to complicate matters [Colour Group] is in a related table so I'll have to think about that). Also I hadn't realised you can have multiple columns in ALLSELECTED so that looks promising too.
However you gave me a lifeline with your second paragraph: I am working with Excel 2016. I have read about VAR but skimmed past it, I'll go back and have a good read. Am I right in thinking I can declare [Staring Index] as a variable and then call it from the measure when it will override the implicit CALCULATE action?
Rgds
Peter

 

[peter789]

Fantastic, VAR did everything I asked it to do!
[TABLE="width: 353"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Oxide[/TD]
[TD]Values[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Na2O %[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sample Date[/TD]
[TD]Colour Group[/TD]
[TD]Average Value[/TD]
[TD]Min of last two[/TD]
[/TR]
[TR]
[TD="align: right"]03/04/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.92[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10/04/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]12.02[/TD]
[TD="align: right"]11.92[/TD]
[/TR]
[TR]
[TD="align: right"]18/04/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.87[/TD]
[TD="align: right"]11.87[/TD]
[/TR]
[TR]
[TD="align: right"]24/04/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.82[/TD]
[TD="align: right"]11.82[/TD]
[/TR]
[TR]
[TD="align: right"]02/05/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.79[/TD]
[TD="align: right"]11.79[/TD]
[/TR]
[TR]
[TD="align: right"]05/05/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.87[/TD]
[TD="align: right"]11.79[/TD]
[/TR]
[TR]
[TD="align: right"]09/05/2017[/TD]
[TD]Green[/TD]
[TD="align: right"]12.20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10/05/2017[/TD]
[TD]Green[/TD]
[TD="align: right"]12.21[/TD]
[TD="align: right"]12.2[/TD]
[/TR]
[TR]
[TD="align: right"]15/05/2017[/TD]
[TD]Green[/TD]
[TD="align: right"]12.30[/TD]
[TD="align: right"]12.21[/TD]
[/TR]
[TR]
[TD="align: right"]19/05/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]12.02[/TD]
[TD="align: right"]12.02[/TD]
[/TR]
[TR]
[TD="align: right"]22/05/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]12.02[/TD]
[TD="align: right"]12.02[/TD]
[/TR]
[TR]
[TD="align: right"]24/05/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.96[/TD]
[TD="align: right"]11.96[/TD]
[/TR]
[TR]
[TD="align: right"]29/05/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.97[/TD]
[TD="align: right"]11.96[/TD]
[/TR]
[TR]
[TD="align: right"]06/06/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.84[/TD]
[TD="align: right"]11.84[/TD]
[/TR]
[TR]
[TD="align: right"]12/06/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.96[/TD]
[TD="align: right"]11.84[/TD]
[/TR]
[TR]
[TD="align: right"]19/06/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.94[/TD]
[TD="align: right"]11.94[/TD]
[/TR]
[TR]
[TD="align: right"]26/06/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.90[/TD]
[TD="align: right"]11.9
[/TD]
[/TR]
</tbody>[/TABLE]

However all it's shown me now is that I hadn't thought my problem right the way through and I need to consider the case where a colour group repeats in the data selected by the table filter! But that's my problem for now!

 

[gazpage]

https://www.sqlbi.com/articles/variables-in-dax/

These guys will explain it better than I ever could. But variables are calculated in the context which they are defined, not when they are called.

Given starting index was working for you on its own I reckon that's your best solution.

 

[gazpage]

Yeah, Variables are great. Make things easy that would otherwise be a massive pain.

Happy to keep trying to help if I can. I note that instead of using LOOKUPVALUE, you could have filtered for the next lowest INDEX.

 

[peter789]

I'm afraid I've got stuck again. I can now calculate the Minimum of the last two values and the Maximum of the last two values, I can then subtract Min from Max and calculate the Moving Range. Now all I need to do is work out the average of the Moving Range in the same way I have been successfully working out average Value using ALLSELECTED. The results are not what I hoped to achieve and I have a hazy idea of why it's happening but I'm baffled as to how I can sort it out. I was hoping the following measure would carry out the calculation; to show what it is actually doing I've changed AVERAGEX to SUMX.

Average Moving Range Selected=
VAR varMR=[Max of last two]-[Min of last two] RETURN
IF(ISBLANK([Moving Range]),BLANK(),CALCULATE(SUMX(QryAnalysis,varMR),ALLSELECTED(QryAnalysis[Sample Date])))

The results are:


[TABLE="width: 766"]
<tbody>[TR]
[TD]Sample Date[/TD]
[TD]Colour Group[/TD]
[TD]Average Value[/TD]
[TD]Average Selected values[/TD]
[TD]Starting Index[/TD]
[TD]Min of last two[/TD]
[TD]Max of last two[/TD]
[TD]Moving Range[/TD]
[TD]Average Moving Range Selected[/TD]
[/TR]
[TR]
[TD="align: right"]02/05/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.79[/TD]
[TD="align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/05/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.87[/TD]
[TD="align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]11.79[/TD]
[TD="align: right"]11.87[/TD]
[TD="align: right"]0.08[/TD]
[TD="align: right"]0.80[/TD]
[/TR]
[TR]
[TD="align: right"]09/05/2017[/TD]
[TD]Green[/TD]
[TD="align: right"]12.20[/TD]
[TD="align: right"]12.24[/TD]
[TD="align: right"]135074[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10/05/2017[/TD]
[TD]Green[/TD]
[TD="align: right"]12.21[/TD]
[TD="align: right"]12.24[/TD]
[TD="align: right"]135074[/TD]
[TD="align: right"]12.2[/TD]
[TD="align: right"]12.21[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]0.03[/TD]
[/TR]
[TR]
[TD="align: right"]15/05/2017[/TD]
[TD]Green[/TD]
[TD="align: right"]12.30[/TD]
[TD="align: right"]12.24[/TD]
[TD="align: right"]135074[/TD]
[TD="align: right"]12.21[/TD]
[TD="align: right"]12.30[/TD]
[TD="align: right"]0.09[/TD]
[TD="align: right"]0.27[/TD]
[/TR]
[TR]
[TD="align: right"]19/05/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]12.02[/TD]
[TD="align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]12.02[/TD]
[TD="align: right"]12.30[/TD]
[TD="align: right"]0.28[/TD]
[TD="align: right"]2.80[/TD]
[/TR]
[TR]
[TD="align: right"]22/05/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]12.02[/TD]
[TD="align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]12.02[/TD]
[TD="align: right"]12.02[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD="align: right"]24/05/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.96[/TD]
[TD="align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]11.96[/TD]
[TD="align: right"]12.02[/TD]
[TD="align: right"]0.06[/TD]
[TD="align: right"]0.60[/TD]
[/TR]
[TR]
[TD="align: right"]29/05/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.97[/TD]
[TD="align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]11.96[/TD]
[TD="align: right"]11.97[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]0.10[/TD]
[/TR]
[TR]
[TD="align: right"]06/06/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.84[/TD]
[TD="align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]11.84[/TD]
[TD="align: right"]11.97[/TD]
[TD="align: right"]0.13[/TD]
[TD="align: right"]1.30[/TD]
[/TR]
[TR]
[TD="align: right"]12/06/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.96[/TD]
[TD="align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]11.84[/TD]
[TD="align: right"]11.96[/TD]
[TD="align: right"]0.12[/TD]
[TD="align: right"]1.20[/TD]
[/TR]
[TR]
[TD="align: right"]19/06/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.94[/TD]
[TD="align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]11.94[/TD]
[TD="align: right"]11.96[/TD]
[TD="align: right"]0.02[/TD]
[TD="align: right"]0.20[/TD]
[/TR]
[TR]
[TD="align: right"]26/06/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.90[/TD]
[TD="align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]11.9[/TD]
[TD="align: right"]11.94[/TD]
[TD="align: right"]0.04[/TD]
[TD="align: right"]0.40[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]

The aggregation seems to be operating on each row but summing itself by the total number of rows in the group (either 3 or 10 in this example). I have tried with and without the use of VAR with no change in the result. I am also concerned that I must be tackling the problem in a very inefficient manner as it is taking nearly 20 seconds to recalculate the table.
As before any help or suggestions would be gratefully received please!

 

[gazpage]

What values are you expecting to see? Seems like you now have the opposite problem that you had before.

varMR is calculated where it is defined, in your case it's in the rows of your table. So varMR in the 05/05 row is 0.08 and is fixed for the rest of the calculation. Your SUMX then cycles through the table with the filter on sample data removed, so again for row 05/05 it is cycling through all the Flint rows (10) and each time adding the pre-calculated 0.08, so a total of 0.8.

I imagine we were to follow the calculation chain back we would probably find that we have iterations within iterations within iterations. I'm afraid improving that is probably beyond my knowledge.

 

[peter789]

I didn't explain that well. I expect the Average Moving Range Selected to look like the Average selected Values column. i.e. All the Flint as 0.82 and the Green as 0.30. I've tried it without the defined Variables and it calculates in exactly the same way as above.