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
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
