Log In
Register
Home
Active Member
February 10, 2020
Offline
I will bring to your attention the following problem:
- in the attached example file, in sheet 1, there would be a list of interventions made on some "machines", the defects found, the probable causes and the costs of the remedies ... The real file is the result of a database query and is much more complex, in meaning that there can be many more "defects" ...
-in sheet 2 are grouped the parts involved in the respective defects on "subassemblies". These "subassemblies" do not appear as such in sheet 1. And the pieces, and the subassemblies are actually more ...
My problem is to complete some centralizers, as I tried to sketch them in sheet 3:
- counting the types of faults on each car. Here, if during an intervention it was acted on several parts of a subassembly, to count only once (eg: Ag5 from 01.01.2020, it was acted on the "ax" and on "bujie", both of the "motor" subassembly , is counted only once ...)
- summary of costs. In this case, all the costs, broken down by "subassemblies" (in the above example "ax" and "bujie" ... must be added 140 to "motor" ...)
Thanks!!!
See Answer
July 16, 2010
Offline
Hi Gigi,
Welcome to our forum. Thanks for sharing your file. It looks like the problem with your data is that it's not in a tabular format, which makes it difficult to summarise. I recommend you fix the layout, then you can use formulas like SUMIFS, COUNTIFS and PivotTables to aggregate the data they way you want.
In the file attached I've used Power Query to unpivot your data. Then I merged the tables in sheet2 with the unpivoted data to bring in the category information in. From there I can use a PivotTable to analyse the data.
Note: the defect and cauza columns in sheet1 contained no data so these were discarded in the unpivot. If your actual dataset has data in these columns then you may need to modify the query. If you'd like to learn more about Power Query you might like to consider my Power Query course.
Mynda
Answers Post
Active Member
February 10, 2020
Offline
Thank you again...
July 16, 2010
Offline
You're welcome! You don't need Power BI for this, you can do everything with Excel. In regards to the merge, you may have got the tables in a different order to mine, plus you need to choose the correct merge type in the merge dialog box. Best to look at my example.
1 Guest(s)
