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

[jonskogland]

Dear community,

Question

How  do I create measures in the pivot table to create colums that calculate the percentage of each dates sum of TEU loaded against the capacity of that routes capacity to find the utlization percentage as well. There is only ever one transport per data in each route.

I'm trying to create this draft table in pivot.

week xx	TEU in total	TEU Loaded %	TEU empty %	Trailer count	TEU space	utlization %
Route 1
Route 2
Route 3
Route 4

The raw data that i have in a table is:

Date

From

To

Orderer

Contract

Ref

Equipment Type

Equipment ID

Net weight

Gross weight

Tara weight

RID

Wagon No

These data are given per each individual wagon

With these raw data colums I've set up in power query to create the calculated colums "route" "number of TEU loaded"  per wagon, "count of trailers" that checks if a wagon is a trailer or not, "week number", "empty". Each of these values could be derived from the equipment type as they denote the amount of TEU capacity per wagon.

the pivot table i've created so far has the colums as is filtered like this per week

	Sum TEU loaded	Empty Wagon	Trailers	TEU in total (including empty TEU Capacity)
Route 1
date 1
date 2
Route 2
date 1
date 2

Besides the Raw data, each route has a specific capacity of TEU. - For example.

Routes	TEU Capacity
Route 1:	45
Route 2:	37

These are written in another table formattet like just written

I've tried to create relationships between the "routes" column in each table. however, when I drag the "TEU capacity" field into the pivot table, it shows the sum of 45 +37 for each dates route, rather than its actual capacity per route. 

[#2]

@jonskogland 

To calculate the percentage of TEU loaded against the capacity of each route in your pivot table, you can create calculated fields or measures.

Here is how you can do it:

1. Ensure that you have a relationship established between your raw data table and the table containing the TEU capacity for each route. Make sure the "routes" column is properly linked in both tables.
2. In your pivot table, right-click on any cell in the "Values" section and choose "Value Field Settings" or "Field Settings" (depending on your Excel version).
3. In the "Value Field Settings" dialog, select the "Sum TEU loaded" field (or the field that represents the sum of TEU loaded) and click on the "Value Field Settings" button.
4. In the "Value Field Settings" dialog, select the "Show Values As" tab.
5. From the list of options, choose "% of Grand Total". This will calculate the percentage of TEU loaded relative to the grand total of all routes.
6. Click "OK" to close the dialogs. Now, the "Sum TEU loaded" column in your pivot table will show the percentage of TEU loaded for each route.
7. To calculate the utilization percentage, you will need to create a new calculated field or measure based on the TEU capacity for each route.
8. Go to the table containing the TEU capacity for each route, and create a new calculated field or measure that calculates the utilization percentage. You can do this by dividing the sum of TEU loaded by the TEU capacity and multiplying by 100.
9. Once you have the utilization percentage calculated, bring this field or measure into your pivot table, ensuring it is associated with the respective routes.
10. Format the newly added fields or measures as percentages in your pivot table if needed.

By following these steps (created with the help of AI), you should be able to calculate the percentage of TEU loaded against the capacity of each route and the utilization percentage in your pivot table.

[#3]

1. I think I've done this correctly. Although the TEU capacity field is summed for each row in the pivot table for some reason, instead of displaying the route specific capacity.
2. an 3. I've already done this.
4. and 5. This is not what I'm looking for, as I need to calculate the percentage of the capacity used for each individual trip made, not in relation to other trips.
6. same as 4 and 5
7. - 8. It calculates a percentage, but this percentage is of the sum of the capacity for all routes, instead of filtering the capacity for each row. The same capacity of 230 is thus used to calculate the perentage for all rows, instead of filtering for each individual row and changing capacity based on the route shown in the left most row.
9. can't do this yet.

[#4]

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

[#5]

There might be some issues with the connection between my two tables then