Pivot Table complicated split percentages

I have created a few pivot tables, but this one has me stumped.
Below is my sample data:
(This is my first post, didn't see how to make images larger to begin with)



I can create the pivot table by client:



But I want to create it by agent.
If you make the agent column A you can't get to 100% for the split because you loose the other agents involved in the product split.
The only way I can see how to do it, involves duplicating the data for each agent making my spreadsheet nearly 200,000 rows.
Is there another way to create the pivot table so it looks like this, given the data I have? Your help is greatly appreciated.

Hi,

I'm not 100% sure what you're trying to do, but I think the below may help

Pivot tables allow you to show values as a percentage of a row total, column total and some other ways as well.

For example, in your pivot table try right-clicking on the 'split' values and clicking 'Value Field Settings'. Then you can click 'Show Values as' and in the drop down select '% of parent row total'. This will calculate each agent's amount as a proportion of the total of the Client/Carrier/Product combination in which they sit.

Be careful interpreting this though - working with percentage values which use different-sized bases can end up with invalid results (for example 100% of a very small category may be less than a 50% split of a very large category - comparing just the percentage values though rather than the absolute amounts can lead to invalid results)

I also added a GIFCoach for this if it helps:
https://www.gifcoach.com/how-to/sho...ages-row-total-column-total-parent-row-total/

Paul hopefully CoachGIF answered you question if not fully answered consider uploading some fake data in excel and will take a quick look.