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?
Bookmarks