Pivot Table - Complicated concept regarding groups

[IIIStan]

Hello Excel Forum.

I am asking for help because I can't think of a logical way to solve my problem.
Here are the details:

We have a factory with machines, making "parts". Some of the machines are in groups for example 6 Presses. Other machines we have only one, for example an oven.
For each machine we record its electricity consumption in kWh.
For the whole factory we record how many parts we make per day.

We make a lot of parts and therefore we need to record kWh/1000 parts.
We then want to compare machines that are similar like the 6 presses, but also group the 6 presses together and then compare them against the oven.

What I have done so far in Excel and now have a problem...
I have a table of dates, machines, machine group, and kWh used, there is also the total factory production in that row.
In a pivot table I then have dates vertically, machine group/machines horizontally and a calculated field of "kWh/production*1000"
And this works perfectly. However...
When I collapse the group of presses it now takes total kWh (which is correct) and divides by total production (which is not correct, it is summing the production 6 times, for the 6 presses) *1000.
For this calculation to be correct I need it to sum the kWh but divide only by 1 x production.

I can give more details to anyone who thinks they could help.

Thank you in advance.

[zbor]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Crooza]

Without seeing your structure I'd be suggesting that you may need to simply identify if there are units in parallel (like your 6 presses) and then identify the fraction of product shared across these parallel units. In this instance each press takes 1/6 th of the total production. Multiplying this out you'll get the accurate machine kW h/ unit produced per machine and adding them together you'll get the total kWh / unit for the group too

[IIIStan]

2015_04_06 - Elec Data Analysis.xlsx

I think the demo file is attached.

Worksheet:Data is obviously just the raw data.
Worksheet:PivotTable is how I would like the data, eventually it would be grouped by year/month.
This is very simplifier showing only 9 machines, in reality we have many many more and more than one line in the factory.

To the right of the pivot table is more explanation about the end result I hope to achieve.

When the Presses group is collapsed, you can see the numbers I would like to get and imagine how the chart would reflect that difference.


I could create a machine called something like "all 6 presses" but then need to have that sum the kWh for the 6 machines, of course this could be done, but I can't think of a clean way to do it with my normalised data.

Thanks!!