Forum Discussion

KarmenN's avatar
KarmenN
Regular Visitor
3 years ago
Solved

DAX calculation

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!

 

 

 

  • MarkLaf's avatar
    MarkLaf
    3 years ago

    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:

     

1 Reply

  • MarkLaf's avatar
    MarkLaf
    Icon for Super User rankSuper User
    3 years ago

    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: