Pivot Table Calculated Field Average and Std Deviation (control chart)

[Minx_Minxy]

Hello,

Apologies if this is in another thread, I did a search on control chart on the threads with no luck.

I am trying to create a pivot table that will provide me with a control chart

I am looking at having a data model with calculated fields named "Control Line", "UCL", "LCL" and "Std. Dev", per the image attached.

In "Data", I have the total count of document IDs we receive for each month divided by 21

Control Line I need the average of Data column

Std.Dev I need the formula =STDEV.S ([Data])

UCL will be = "Control Line"+("Std.Dev"*3)

LCL = "Control Line"- ("Std.Dev"*3)

I have tried adding the average for the "Data" column but all I am getting is errors. Nothing is working.

Help is very much appreciated.

 

[Sergius]

Hello!
Can I have a small example of your original table with test data?

 

[Minx_Minxy]

Hello!
Can I have a small example of your original table with test data?

Here

M + Y	Date	Doc ID
Oct2022	02/10/2022	54902366
Oct2022	02/10/2022	54902365
Oct2022	02/10/2022	54902365
Oct2022	02/10/2022	54902365
Oct2022	02/10/2022	54902364
Oct2022	02/10/2022	54902364
Oct2022	02/10/2022	54902364
Oct2022	02/10/2022	54902364
Oct2022	03/10/2022	2000498080
Oct2022	03/10/2022	2000498064
Oct2022	03/10/2022	2000498011
Oct2022	03/10/2022	2000498011
Oct2022	03/10/2022	54903096
Oct2022	03/10/2022	54903096
Oct2022	03/10/2022	54903025
Oct2022	03/10/2022	54903024
Oct2022	03/10/2022	54902990
Oct2022	03/10/2022	54902989
Oct2022	03/10/2022	54902988
Oct2022	03/10/2022	54902987
Oct2022	03/10/2022	54902982
Oct2022	03/10/2022	54902959
Oct2022	03/10/2022	54902944
Oct2022	03/10/2022	54902944
Oct2022	03/10/2022	54902921
Oct2022	03/10/2022	54902920
Oct2022	03/10/2022	54902919
Oct2022	03/10/2022	54902918
Oct2022	03/10/2022	54902913
Oct2022	03/10/2022	54902678
Oct2022	03/10/2022	54902673
Oct2022	03/10/2022	54902673

 

[Sergius]

Hello! The point is that measures cannot directly use other measures or a calculated column in formulas. There may be a way around this, but I don't know it.
I apologize if I gave you hope of resolving the issue.

 

[Sergius]

Hello! After studying the manuals a little, you can still solve your issue. In particular, this resource gave food for thought.
That is, using the VALUES parameter you can solve issues.
VALUES is one way to create your own table (a table with one column with unique values).
Here's what I got

M + Y	Date	Doc ID					Row Labels	Data	Control Line	UCL	LCL	Std.Dev
Oct2022	02.10.2022	54902364					Oct2022	1,52	1,52	1,523809524	1,52	0,00
Oct2022	02.10.2022	54902364					Nov2022	0,10	0,10	0,095238095	0,10	0,00
Oct2022	02.10.2022	54902364					Grand Total	1,62	0,81	2,952380952	-1,33	0,71
Oct2022	02.10.2022	54902364
Oct2022	02.10.2022	54902365
Oct2022	02.10.2022	54902365

Measures that have been created
Data

=COUNT(Table1[Doc ID])/21

Control Line

=AVERAGEX(VALUES(Table1[M + Y]); [Data])

UCL

=[Control Line]+([Std.Dev]*3)

LCL

=[Control Line]-([Std.Dev]*3)

Std.Dev

=STDEVX.P(VALUES(Table1[M + Y]); [Data])

Of course, this is all at a primitive level, but the direction is what you need.
P.S. I was interested in figuring this out myself. There is room to move. Good luck.