How to calculate percentage of a summed column against another tables values

jonskogland 

To calculate the percentage of each date's sum of TEU loaded against the capacity of each route in your pivot table and find the utilization percentage, you can follow these steps:

1. Make sure you have established the relationships between the "routes" column in your raw data table and the table containing the TEU capacity for each route.
2. In your pivot table, add the following fields to the "Values" area:
• "Sum TEU loaded" (sum of TEU loaded per date and route)
• "TEU Capacity" (capacity of each route)
3. Right-click on the "Sum TEU loaded" field in the pivot table and select "Value Field Settings" or "Field Settings" (depending on your Excel version).
4. In the "Value Field Settings" dialog, choose "Show Values As" and then select "% of Parent Row Total." This will calculate the percentage of TEU loaded against the sum of TEU loaded for each date.
5. Click "OK" to close the dialog. Now, the "Sum TEU loaded" field in your pivot table will display the percentage of TEU loaded for each date relative to the total sum of TEU loaded for that date.
6. Right-click on the "TEU Capacity" field in the pivot table and select "Value Field Settings" or "Field Settings."
7. In the "Value Field Settings" dialog, choose "Show Values As" and then select "% of Parent Row Total." This will calculate the percentage of TEU capacity against the sum of TEU capacity for each date.
8. Click "OK" to close the dialog. Now, the "TEU Capacity" field in your pivot table will display the percentage of TEU capacity for each date relative to the total sum of TEU capacity for that date.
9. Add the "Empty Wagon" and "Trailers" fields to the "Values" area of the pivot table.
10. Create a calculated field called "TEU in total" that sums the TEU loaded and the empty TEU capacity for each date. The formula for this calculated field would be something like:

TEU in total = [Sum TEU loaded] + [Empty Wagon]

1. Create another calculated field called "Utilization %" that calculates the percentage of TEU loaded against the TEU in total for each date. The formula for this calculated field would be:

Utilization % = [Sum TEU loaded] / [TEU in total] * 100

1. Format the calculated fields as percentages if needed.

With these steps, you should have the desired columns in your pivot table: "TEU in total," "TEU Loaded %," "TEU empty %," "Trailers," "TEU space," and "Utilization %." The "TEU Loaded %" column will show the percentage of TEU loaded against the TEU in total for each date, and the "Utilization %" column will display the utilization percentage for each date based on the TEU loaded and the empty TEU capacity. In the heat of the moment and the lack of time, this text was created with the help of AI.