% calculation with different total for each level of aggregation

jorgerrgg

New Member
Joined
Mar 9, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

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
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 9, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
Glad to hear you worked it out.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top