+ Reply to Thread
Results 1 to 4 of 4

pivot table - count values greater than some number, and display percentage of total

  1. #1
    Registered User
    Join Date
    06-12-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2013
    Posts
    2

    pivot table - count values greater than some number, and display percentage of total

    Hi,

    First, I'm new to pivots and tables. Here's an example file with a simple table (1st sheet) and a pivot table (2nd sheet) using data from it:

    pivot.xlsx

    The pivot table in the 2nd sheet aggregates per name. I would like two other columns in the pivot to display the following:
    - the number of cells in each name that are greater than 5
    - the fraction of the total count represented by the above, i.e. number of cells > 5 in each name divided by total number of cells in each name (not the grand total)

    In effect, the table should output:

    foo 2 0.5
    bar 2 0.5
    chi 2 0.4

    The dumb method I came up with was to add one column "gt5" to the initial table with the formula "=IF(@[nr]>5,1,0)" and another column "tot" with the formula "=1". Then in the pivot table, I create a calculated field "per" with the formula "=gt5/tot". I add "name" as rows, "gt5" and "per" as values.

    My actual data set has hundreds of thousands of rows and about 35 columns so I'm interested in doing this without having to add more columns in the initial table if possible. I'd also like to avoid filters in the pivot on the "nr" column (in the actual data I'd like to have it all available for other pivot calculations).

    Could a kind soul enlighten me? Also, how can one add a smarter calculated field in the pivot table to show the counts of values per "name" that are greater than 5 without adding extra columns in the initial table? Is it possible?

    Thanks in advance!

    Cheers.

    p.s. Also, I admit I haven't read a lot (though I did a bit) but i understand that the calculated field always uses the SUM of the field in the formula. For instance, I tried adding a calculated field with the formula "=IF(nr>5,1,0)" and then display that summarized as Sum, but that doesn't work as expected ... it always shows "1". I also tried "=nr-5", and it gives the sum of all aggregated values less 5 (doesn't matter whether i summarize it as Count or Sum in the pivot). Am I missing something?
    Last edited by mastabog; 06-12-2013 at 07:39 PM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,063

    Re: pivot table - count values greater than some number, and display percentage of total

    Hi mastabog and welcome to the forum,

    See the attached file with an answer to your problem. I hope this is what you wanted. I needed a helper column, but I think it will work for you.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    06-12-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2013
    Posts
    2

    Re: pivot table - count values greater than some number, and display percentage of total

    Thanks for replying. I had already done that myself and it's not the best solution because the division column that you added next to the pivot table is not part of the pivot table, which means you can't sort the pivot table on it (try it).

    As such, my solution posted above is better, i.e. 2 helper columns (gt5 and tot), in the initial table and one calculated field (per) in the pivot table which is then part of the pivot table and you can sort on it. See attached.

    pivot2.xlsx

    I'd like to know whether this is possible without any helper columns in the initial table.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,063

    Re: pivot table - count values greater than some number, and display percentage of total

    Hi,

    I tried filters and calculated fields and was unable to do your problem without helpers. I think helper columns are the way to do the problem.

    Maybe some of the smart guru's would have a better method. BTW - I'm sure I could do this problem with VBA and a Script.Dictionary but that is a lot harder than helper columns and Pivots.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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