cancel
Showing results for
Did you mean: Frequent Visitor

## Ignore measure subgroups when charting and in calculations?

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

1 ACCEPTED SOLUTION Frequent Visitor
```xbar:=CALCULATE ( SUM ( [Value ), ALLSELECTED( Table ) )
/ CALCULATE ( COUNT (Table[Batch]), ALLSELECTED( Table) )```

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

2 REPLIES 2 Frequent Visitor
```xbar:=CALCULATE ( SUM ( [Value ), ALLSELECTED( Table ) )
/ CALCULATE ( COUNT (Table[Batch]), ALLSELECTED( Table) )```

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

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/

Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!

Join the conversation at We Talk BI find out more about me at Slow BI  