How to calculate average of multiple categories

[Anonymous]

Hello all,

I want calculate average of multiple categories:

Here is the scenario

table_1

Plant	Station	Machine	Volume	Reject
A	x	x1	150	3
A	x	y1	150	5
A	x	z1	150	1
A	x	t1	150	7
A	y	x1	200	9
A	y	q1	200	10
B	x	y1	250	8
B	x	t1	250	7
B	x	x1	250	1

I have 2 factories 3 stations and several machines for each station.

Volume is equal to station's volume so the rows are duplicated

Rejects are basically the reject amount for each machine.

Below table is the explanation of what I want to tell above.

table_2

Plant	Station	Volume	RejectTotal	Ratio
A	x	150	16	11%
A	y	200	19	10%
B	x	250	16	6%

But the data format is as table_1

I want to calculate reject ratio as in table_2 for each plant_station pairs but I can't sum all volume because those values are duplicated in table_1

What I want to achieve is : create average for each plant_station pairs and divide to sum of reject totals.

How can I achieve that?

I'm quite new in DAX environment. I looked up some examples but I couldn't make it. Little explanation would be great!

Thanks in advance.

Cheers.

[v-yangliu-msft · Accepted solution]

Hi  @tyr , 

Here are the steps you can follow： 

 1. Create measure. 

avg =  
CALCULATE(AVERAGE('Table'[Volume]),ALLEXCEPT('Table','Table'[Plant],'Table'[Station]))  

 
RejectTotal =  
CALCULATE(SUM('Table'[Reject]),ALLEXCEPT('Table','Table'[Plant],'Table'[Station])) 

Ratio =  
DIVIDE('Table'[RejectTotal],'Table'[avg])  
 

2. Result. 

You can downloaded PBIX file from here. 

Best Regards, 

Liu Yang 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[FrankAT]

Hi @Anonymous 

you can do it like this:

Reject Total = 
CALCULATE(
    SUM('Table'[Reject]),
    ALLEXCEPT('Table','Table'[Plant],'Table'[Station])
)

Ratio = DIVIDE([Reject Total],MIN('Table'[Volume]))

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Anonymous]

@FrankAT 

Thanks for the answer.

But I think I couldn't explain it clearly.

Table_2 is just explanation of what I need, table1 is actually how it is.

So it isn't  possible to work on table_2.

Thanks a lot for your effort,

Kind regards,

[amitchandak]

@Anonymous ,

volumn total = sumx(summarize(Table, Table[plant], table[station], "_1",calculate(Max(Table[Volume]))),[_1])

reject total = sum(Table[reject])

reject % = divide([reject total],[volumn total])

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[Anonymous]

@amitchandak  thanks for the answer.

What is the purpose of "_1" ?

If it is suffix of stations, how can I generalise it?

In real data there is no pattern for station name unfortunately.

Thanks again.