Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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.
2) Select and add sio2graphs. It will appear here when added.
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.
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)
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:
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |