The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to 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.
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.
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)
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,
@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.
@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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
95 | |
80 | |
62 | |
56 |
User | Count |
---|---|
252 | |
121 | |
112 | |
81 | |
70 |