cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrlang02
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.  

 

BatchSampleValue
AA1200.1
AA2300.5
AA3134.7
BB1200.1
BB2300.5
CC1250.5
CC250.7
CC3692.1
CC4237.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.  

https://ibb.co/cwLJzLn

Is there a way to use the measure in the way described or do I need to take a different approach??

1 ACCEPTED SOLUTION
mrlang02
Frequent Visitor

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

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

View solution in original post

2 REPLIES 2
mrlang02
Frequent Visitor

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

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

kentyler
Solution Sage
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


Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors