+ Reply to Thread
Results 1 to 2 of 2

Calculating “Share Of” Category / Row & Subtotal in Pivot Table in Excel

  1. #1
    Registered User
    Join Date
    12-04-2018
    Location
    Mars
    MS-Off Ver
    2016
    Posts
    1

    Calculating “Share Of” Category / Row & Subtotal in Pivot Table in Excel

    I want to have one column in a pivot table that is as a calculated item from the total count to display the share of total in that category. I am able to 'hack' pivot tables in a way that I get a total count for a field (which consists of variable values) and can calculate the share for a category. Custom field: count / (total_count / count). Total_count contains the total count for each category on each row of the input data.

    Now, I am but having issues to calculate the share for a subtotal. As the hack is specifically designed to aid in calculating the share of a category, it breaks for the subtotal.

    Highlighted in red the fields that are incorrect:

    ztmSb.png

    Below is a mock-up concept with correct output:

    q4qX6.png

    But the end result needs to be 'simple', so it can be broken down by each week:

    VMgHA.png

    The difference between 2 weeks can be a simple formula outside the pivot table, or can probably be a calculated item. That is not in focus here.

    Thought starters are welcome! What is the direction for a proper, unhacked Excel method?

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Calculating “Share Of” Category / Row & Subtotal in Pivot Table in Excel

    Hello Aldert and Welcome to Excel Forum.
    While I am not sure that I can help with what you are attempting, I can suggest that it will be easier for someone to help if you could upload a desensitized sample of the workbook from which the screen shots are taken.
    To upload a sample workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] calculating specific data from a pivot table to create a pivot chart in Excel 2016
    By cinstanl in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-15-2016, 12:14 PM
  2. DISPLAY SUBTOTAL and SUBTOTAL % in PIVOT TABLE
    By excelmr in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-11-2015, 05:44 AM
  3. Changing a pivot chart column color based on the pivot table category
    By Shawne in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-08-2014, 09:12 AM
  4. Replies: 1
    Last Post: 05-16-2014, 07:34 PM
  5. Replies: 4
    Last Post: 07-28-2012, 10:32 AM
  6. [SOLVED] Excel Pivot Table Row Subtotal
    By ddecoste in forum Excel General
    Replies: 1
    Last Post: 09-13-2005, 08:05 PM
  7. display data as a percentage of a subtotal in excel pivot table
    By Fl pivot user in forum Excel General
    Replies: 2
    Last Post: 03-26-2005, 09:07 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1