How to calculate a defect rate?

[jomorya]

Hi. I´m kind of new to power BI, and I need some help.

Our product could have a lot of defects in every piece and I need a single measure to make a paretto graph to calculate the % of every kind of defect per piece.

I´ve got a table like this one:

Serial number	Defects	Defect Qty
220210142962	Without defects	1
220210142960	Without defects	1
220210142951	Without defects	1
220210142945	S-01	1
220210142945	C-02	1
220210142945	R-05	1
220210142938	D-01	1
220210142937	Without defects	1
220210142935	C-02	1
220210142935	V-02	1
220210142929	Without defects	1

Thanks in advance

[tom480]

Hi @jomorya ,

Here's one solution, l hope you find this helpful!

1)  The first thing I did was go to the visual app store and get a pareto visual.  Click on the three dots then select Get more visuals.  Next search for pareto and select the one I selected next.

Get more visuals

2)  Select and add sio2graphs.  It will appear here when added.

select sio2Graphs and add

3)  I added your table data.  Click on 'Format your visual' to edit the appearance.

4)  In Power Query, I made a duplicate copy of the table and grouped by defect, sorted by frequency and added a an index.

5)  Next the relationship was already created based on the defects.

6)  Last step, I created a measure that provides the pareto and corresponds to the pareto in the visual.

Pareto % =

VAR vDefect =

SELECTEDVALUE ( BinTotals[Index] )

VAR vResult =

CALCULATE (

[TL Defects2],

BinTotals[Index] <= vDefect,

ALL ( BinTotals[Defects])

)

RETURN

DIVIDE ( vResult , [TL Defects] )

I have provided a copy of the PBIX for reference here.  

If you found this useful, please mark as a solution to help others find it too!  I also accept kudos.  Enjoy!  Tom 😀

[jomorya]

Hi @tom480 

Thanks a lot for your answer but this gives me the % of total defects and I need to calculate the % of different defects per piece, in this case I have 7 pieces(some pieces have more than one defect) & 11 defects.

I tried to divide the amount of different defects by the number of pieces but it gives me incorrect data.

[v-xiaotang]

Hi @jomorya 

"I need to calculate the % of different defects per piece", you can try this measure, it calculates the percentage of defect in each piece

Measure1 = //calculate the % of different defects per piece
var _amountDiffDefects= CALCULATE(DISTINCTCOUNT('Table'[Defects]),ALLEXCEPT('Table','Table'[Serial number]))
var _numOfPieces= CALCULATE(DISTINCTCOUNT('Table'[Serial number]),ALLEXCEPT('Table','Table'[Serial number]))
return divide(_numOfPieces,_amountDiffDefects)

Best Regards,

Community Support Team _Tang

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

[jomorya]

Thanks a lot @v-xiaotang  but this is not what I need.

I´ll try to explain it better:

Once I have the quantity of defects:

I need to divide the total amount of different defects by the total amount of pieces, if I use a measure:

% defects = divide(COUNT(Tabla[Defect Qty]), DISTINCTCOUNT(Tabla[Serial number]),0)

It returns me this result:

The total amount it´s Ok, 11 different defects divided by 8 different pieces=137.5%, but the % per type of defect doesn´t show what I need, for example for Without defects would be 5/8=62.5%.

I´ll hope this will clarify my question.

Thanks in advance