Dynamic mean for control charts based on rules
Hello, I am trying to create a control chart based on a set of rules. My mean and standard deviation should change based on set of mentioned rules. Standard rules to calculate the control chart are as follows 1. to calculate mean/ average the minimum data points are 5. 2. To calculate standard deviation the minimum data points are 15. The required rules for the visualization 1. If seven consecutive points appear above or below the MEAN, the MEAN for the past seven points is recalculated. This is where we would look at the seven points ahead from the first point where it changed. 2. If seven consecutive points are moving either up or down the MEAN is recalculated on the past seven points. This again is where we would look at the seven points ahead from the first point where it changed. 3. If fifteen consecutive points are within +/- 1.0 standard deviation of the MEAN, the standard deviation, UCL and LCL are recalculated. Beginning to the system change point (back to the start of the 15 data points that triggered the recalc). Newly calculated means and standard deviations are used for rule evaluation on the next data points Sample data is as follows: Fiscal_Week Returns 5/7/2022 4630 5/14/2022 4800 5/21/2022 5069 5/28/2022 2789 6/4/2022 4596 6/11/2022 5275 6/18/2022 5172 6/25/2022 5219 7/2/2022 5101 7/9/2022 5020 7/16/2022 5327 7/23/2022 5001 7/30/2022 5221 8/6/2022 5309 8/13/2022 5225 8/20/2022 5475 8/27/2022 5291 9/3/2022 5371 9/10/2022 4961 9/17/2022 5091 9/24/2022 5355 10/1/2022 4049 10/8/2022 4729 10/15/2022 4869 10/22/2022 4417 10/29/2022 4030 11/5/2022 3698 Sample graph : Note: Not based on above mentioned data amitchandak Greg_Deckler Mikelytics mangaus1111
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
