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

1. ## 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?

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?

2. ## 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.

3. ## 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. ## 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.

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

#### 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