+ Reply to Thread
Results 1 to 8 of 8

accumulative count of items in pivot

  1. #1
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    accumulative count of items in pivot

    Hi all,

    I need to count the total number of Materials and then get the accumulative % of each of the total, while ranking it by the value field "Sell Through".

    e.g.

    There are 100 Materials in total, hence, 1 Material accounts for 1% of the total. The first 5 (ranked by Sell Through) account for 5%, the first 10 account for 10% of the total etc...

    I am currently using a helper column to do that (column A) but want to do it within the pivot as the data keeps changing/expanding. Is that feasible somehow?

    fdsfdsfdsfds.PNG

  2. #2
    Valued Forum Contributor
    Join Date
    11-02-2016
    Location
    NY
    MS-Off Ver
    2010
    Posts
    459

    Re: accumulative count of items in pivot

    I created an additional column with the% in each cell (your example .01). Add to Pivot table range. Created Calculated Item for the new field (NewField / 100 in your example). Then I had .01 in each Column. Changed the NewColumn to '% Running total in' (Under the SHOW VALUE AS in Pivot table).

  3. #3
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: accumulative count of items in pivot

    Quote Originally Posted by queuesef View Post
    I created an additional column with the% in each cell (your example .01). Add to Pivot table range. Created Calculated Item for the new field (NewField / 100 in your example). Then I had .01 in each Column. Changed the NewColumn to '% Running total in' (Under the SHOW VALUE AS in Pivot table).
    I'm not sure what you mean by additional column (in the data set?) and calculated item (why divided by 100? This isn't a fixed value unfortunately)? I have simplified my data and mocked up a sample file to better illustrate what I am trying to achieve!
    Attached Files Attached Files
    Last edited by esbencito; 12-03-2018 at 11:42 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    11-02-2016
    Location
    NY
    MS-Off Ver
    2010
    Posts
    459

    Re: accumulative count of items in pivot

    Originally, there was no file attachment. Assumed there were 100 items as stated, then each would have .01. The extra column was put in to accommodate the Exact percentage.

  5. #5
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: accumulative count of items in pivot

    So I assume there's no way to do that in a pivot then?

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

    Re: accumulative count of items in pivot

    While I can't help you produce a column inside the pivot table that will do what you want, I can suggest a more automated formula for K7 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    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.

  7. #7
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: accumulative count of items in pivot

    Thanks JeteMuc! not as smooth as having it directly in the pivot, but it does the job for now.

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

    Re: accumulative count of items in pivot

    You're Welcome. I hope that you have a blessed day.

+ 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. Pivot table sum of TAT/ count of items
    By Apple Ling in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-16-2017, 12:33 PM
  2. Count Multiple Items in a Pivot Table filter
    By Karen13 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-16-2015, 05:49 AM
  3. Pivot Chart - Display data in X axis (all) items regardless of count
    By gtp2014 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 10-22-2014, 11:50 AM
  4. Replies: 0
    Last Post: 07-29-2014, 08:31 AM
  5. Replies: 2
    Last Post: 09-26-2012, 01:41 PM
  6. Pivot Items Count varies on multiple runs
    By narendramaruti in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-21-2009, 07:38 PM

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