Results 1 to 4 of 4

Thread: Pivot Table complicated split percentages

  1. #1

    Pivot Table complicated split percentages

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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)

    Click image for larger version. 

Name:	Capture1.PNG 
Views:	23 
Size:	24.1 KB 
ID:	6345

    I can create the pivot table by client:

    Click image for larger version. 

Name:	Capture2.PNG 
Views:	17 
Size:	21.1 KB 
ID:	6346

    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.

    Click image for larger version. 

Name:	Capture4.PNG 
Views:	21 
Size:	39.2 KB 
ID:	6347

  2. #2

    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)

  3. #3

  4. #4
    Conjurer Ed Kelly's Avatar
    Join Date
    Jul 2016
    Excel Version
    Paul hopefully CoachGIF answered you question if not fully answered consider uploading some fake data in excel and will take a quick look.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts