Hi!
Question:
Values in picture below under "Average of Amount 100 % Occupancy" is set to average. Is there a workaround for keeping the values for the level 11145 and 11150 to show average while the levels above (111 - Xspedition and 1000 - Delivery) shows the sum of 11145 and 11150, that is 138 000 + 119 600 = 257 600 (business 1000)?
I know I can change just the two upper levels to sum instead of average but since it points to the original data in the source table, wrong values will appear.
In my world the easiest way would be to have a function in the pivot table that just sums the values shown below, like showing a subtotal of a range in a regular table, instead of going back to the source table.
Any suggestions?
More information (if needed):
In the pivot table below I am getting my data from a separate source table in one sheet. This table is setup to search (vlookup) for a specific vehicle type (ie Heavy truck 16 ton) in a price list in another sheet and return the price per hour. When the price for the specific vehicle is found it multiplies it with the possible working hours for that month (also vlookup). So, if there is 184 working hours in a month whereas the price is 650 per hour, the total possible revenue for 100 % occupancy of a vehicle is 119 600. This is shown in the column "Average of Amount 100 % occupancy".
Unfortunately there are several order rows for the vehicle 11145 and 11150 in the source table. Due to several order rows the amount for 100 % Occupancy occurs several times which is the reason that I have to choose "Average" in the pivot table" do display the correct value for 100 % occupancy. The real earned revenue, "Sum of Revenue", is summed up correctly of course.
If a resource (11145, 11150) could only appear under one business (ie only 1000 instead of 1000, 2100, 2120) I would be able to come up with a workaround (sum instead of average) in the source table by dividing ie 119 600 for 11145 by quantity of order rows for that vehicle. Unfortunately I am kind of stuck in this situation since you just cannot say that ie 50 000 belongs to business 1000 and 69 600 to business 2100 for vehicle 11145 and so on... All business could expect to have a 100 % occupancy even though the real total Revenue is spread over several businesses.
Question:
Values in picture below under "Average of Amount 100 % Occupancy" is set to average. Is there a workaround for keeping the values for the level 11145 and 11150 to show average while the levels above (111 - Xspedition and 1000 - Delivery) shows the sum of 11145 and 11150, that is 138 000 + 119 600 = 257 600 (business 1000)?
I know I can change just the two upper levels to sum instead of average but since it points to the original data in the source table, wrong values will appear.
In my world the easiest way would be to have a function in the pivot table that just sums the values shown below, like showing a subtotal of a range in a regular table, instead of going back to the source table.
Any suggestions?
More information (if needed):
In the pivot table below I am getting my data from a separate source table in one sheet. This table is setup to search (vlookup) for a specific vehicle type (ie Heavy truck 16 ton) in a price list in another sheet and return the price per hour. When the price for the specific vehicle is found it multiplies it with the possible working hours for that month (also vlookup). So, if there is 184 working hours in a month whereas the price is 650 per hour, the total possible revenue for 100 % occupancy of a vehicle is 119 600. This is shown in the column "Average of Amount 100 % occupancy".
Unfortunately there are several order rows for the vehicle 11145 and 11150 in the source table. Due to several order rows the amount for 100 % Occupancy occurs several times which is the reason that I have to choose "Average" in the pivot table" do display the correct value for 100 % occupancy. The real earned revenue, "Sum of Revenue", is summed up correctly of course.
If a resource (11145, 11150) could only appear under one business (ie only 1000 instead of 1000, 2100, 2120) I would be able to come up with a workaround (sum instead of average) in the source table by dividing ie 119 600 for 11145 by quantity of order rows for that vehicle. Unfortunately I am kind of stuck in this situation since you just cannot say that ie 50 000 belongs to business 1000 and 69 600 to business 2100 for vehicle 11145 and so on... All business could expect to have a 100 % occupancy even though the real total Revenue is spread over several businesses.
Last edited:
