Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello!
I am trying to find out how to calculate "rate for not OK products", so I created a sample data.
I have 2 main tables:
1)All produced orders, together with production operations, date, qty
2)Reported not OK products together with operation (where defect was found), date, qty
To connect date between these tables, I have created:
1)Date table
2)List with all manufacturing orders
3)List with all possible production operations
I also made 2 tables.
First one shows how many pcs reported as "not OK products" in each operation for each manufacturing order. Second one shows how many pcs produced in each operation in total:
Now I would like to calculate "rate for not OK products" for each MO in each operation.
For example order 3447
*100pcs reported in total in pre.test operation, - reported as "Not OK", rate should be 0/100*100=0%
*90pcs reported in EOL operation, 12pcs reported as "not ok", rate should be 12/90*100=13,33%
Calculation itself is very easy, but how to do it with DAX in PowerBI, so I would get correct value for each operation?
Thanks!
Solved! Go to Solution.
Make sure you use the fields from your dimension tables ('Description1', 'list of allMO-s') and then you should be able to drop the below measure in to get what you want (if I'm understanding your requirements).
rate for not OK products =
DIVIDE( COUNTROWS( 'QA data' ), COUNTROWS( 'tehtud MO-d' ) )
Note that this is just counting the related rows. If you have value columns in 'QA data' and/or 'tehtud MO-d' that you want to sum, then replace the COUNTROWS on a table with SUM on a column (e.g., SUM( 'QA data'[Total_QTY] ) )
To get the percent formatting, use the measure formatting settings:
Make sure you use the fields from your dimension tables ('Description1', 'list of allMO-s') and then you should be able to drop the below measure in to get what you want (if I'm understanding your requirements).
rate for not OK products =
DIVIDE( COUNTROWS( 'QA data' ), COUNTROWS( 'tehtud MO-d' ) )
Note that this is just counting the related rows. If you have value columns in 'QA data' and/or 'tehtud MO-d' that you want to sum, then replace the COUNTROWS on a table with SUM on a column (e.g., SUM( 'QA data'[Total_QTY] ) )
To get the percent formatting, use the measure formatting settings:
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
10 | |
6 |