# % calculation with different total for each level of aggregation

#### jorgerrgg

##### New Member
Good morning everyone.

In my Excel file I have an "Orders" table which among other things includes the field "Hours needed", which is basically the time in hours required to complete the order and the "Date of order" which is the date when the order was carried out in dd/mm/yyyy format. You can have multiple orders a day.

I am currently developing a dashboard which will showcase the "Ocupation rate" as a %. This ocupation rate is based on the maximum number of hours that are workable during this time period. To create this graph I would need a table which contains the different time periods in rows and the associated "Ocupation %" as a value.

The workable amount of hours changes based on the level of aggregation. The levels of aggregation Im working with, as well as their workable hours are shown below:
- Year: 1560 h
- Quarter: 390 h
- Month: 130 h

All levels of aggregation are included on the Pivot Table row selection in order to allow for the user to expand or collapse the levels of aggregation with the + and - buttons on the graph.

In theory it should be as simple as:

% ocupation rate:=SUM(Table1[Hour needed])/130 and then defining that the agregation between periods is done based on the average instead of the sum. The average of the ocupations of each month in a quarter is equal to the ocupation of the quarter after all.

This has 2 problems:
- I wouldn't want that the aggregation between levels is always done based on the average affecting the whole workbook as there are other statistics such as the "Total Revenue" which aggregate in sums.
- I haven't been able to change the aggregation procedure anyway.

Any help is greatly appreciated.

Cheers

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### ExcelGzh

##### Board Regular
Could you send some details of the layout of your spreadsheet (screenshot would be good)? For instance do you have a row for each month with a sub-total for each quarter and a grand total for the year?

#### jorgerrgg

##### New Member
Could you send some details of the layout of your spreadsheet (screenshot would be good)? For instance do you have a row for each month with a sub-total for each quarter and a grand total for the year?
Thank you for your answer, in the end I was able to solve it with a combination of summarice and averagex

#### ExcelGzh

##### Board Regular
Glad to hear you worked it out.

Replies
0
Views
389
Replies
0
Views
423
Replies
3
Views
6K
Replies
9
Views
79
Replies
3
Views
679

1,127,967
Messages
5,627,914
Members
416,282
Latest member
fchagas97

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back