I am attempting to use Power Bi to make dynamic X and Rbar control charts that are used in quality control, statistical process control. I don't want to get to jargon-y, so I will try to explain the specific calculations I need to make without getting to far into control charts. But if you want more info, you can look here https://www.spcforexcel.com/knowledge/variable-control-charts/xbar-r-charts-part-1.
Here is an example of my data structure.
where Batch is a unique product batch, Sample is a sample of a manufactured item within that batch, and Value is some quantifiable measure (weight, length, concentration, etc).
To make my xbar and R chart, I need to be able to calculate and plot:
xbar- this is the overall average of the batch averages. So if I took the average value of batch A, B and c separately, then averaged those 3 values
rbar- this is the overall average of the batch value rage. So if I took the range value of batch A, B and C separately, then averaged those 3 values
I am able to use code like that below to calculate xbar as a measure.
However, if I try to plot this value with Batch vs. Value on the x and y axes, the value of xbar will be evaluated at the specified level of batch. Instead, I need the value of xbar that is the average of all batch averages -- which will be the same for all batches.
Is there a way to use the measure in the way described or do I need to take a different approach??