Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jomorya
Regular Visitor

How to calculate a defect rate?

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 numberDefectsDefect Qty
220210142962Without defects1
220210142960Without defects1
220210142951Without defects1
220210142945S-011
220210142945C-021
220210142945R-051
220210142938D-011
220210142937Without defects1
220210142935C-021
220210142935V-021
220210142929Without defects1

 

Thanks in advance

4 REPLIES 4
tom480
Resolver I
Resolver I

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 visualsGet more visuals

 

 

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

 

select sio2Graphs and addselect sio2Graphs and add

 

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

 

defects3.png

 

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

 

defects5.png

 

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

 

defects4.png

 

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

 

defects6.png

 

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 😀

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.

 

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)

vxiaotang_0-1652779480018.png

 

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.

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:

jomorya_0-1652860481884.png

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:

jomorya_1-1652860889819.png

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.