Ignore measure subgroups when charting and in calculations?

mrlang02 — Wed, 23 Oct 2019 16:38:35 GMT

Hi all,

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.

Batch	Sample	Value
A	A1	200.1
A	A2	300.5
A	A3	134.7
B	B1	200.1
B	B2	300.5
C	C1	250.5
C	C2	50.7
C	C3	692.1
C	C4	237.9

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.

xbar:=averagex(summarize(Table,[Batch],"Example",average([Value])),[Example])

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??

Re: Ignore measure subgroups when charting and in calculations?

kentyler — Tue, 08 Oct 2019 20:52:49 GMT

you can use ALL() in dax to remove any filter context from a table

xbar:=averagex(summarize(Table,[Batch],"Example",average([Value])),[Example])

using ALL(Table) might work for you

but SUMMARIZE is a lot more complicated than it looks

if you want to go into it and understand just what is going on take a look at https://www.sqlbi.com/articles/all-the-secrets-of-summarize/

Re: Ignore measure subgroups when charting and in calculations?

mrlang02 — Wed, 23 Oct 2019 16:28:42 GMT

xbar:=CALCULATE ( SUM ( [Value ), ALLSELECTED( Table ) )
    / CALCULATE ( COUNT (Table[Batch]), ALLSELECTED( Table) )

For those interested, this was the solution to my problem.

topic Ignore measure subgroups when charting and in calculations? in DAX Commands and Tips

Ignore measure subgroups when charting and in calculations?

Re: Ignore measure subgroups when charting and in calculations?

Re: Ignore measure subgroups when charting and in calculations?