Forum Discussion
Performing calculations on Pivot Table data
Hi,
I am using pivot tables to look at data from a large set and I want to sum the largest 5 values in a pivot table column and calculate it's % of the total of that column. If the range was static then I would just use SUMPRODUCT(LARGE(AD5:AD22,5)) and then divide this by the cell that contains the Total to get the % figure however I have a Slicer linked to the Pivot Table and so every time I choose a different option in the slicer then the range of the Pivot table will change and I can't seem to reference columns as I would in a normal table?
thanks
Row Labels Sum of Principal
| BlackRock | 359150000 |
| Deutsche | 21971551 |
| Dreyfus | 221000000 |
| Federated | 300400000 |
| First American | 3729000 |
| Goldman Sachs | 63000000 |
| JP Morgan | 100000000 |
| Northern Trust | 15422000 |
| Putnam | 11999050 |
| Schwab | 772000000 |
| State Street | 299878802 |
| T Rowe Price | 23700000 |
| UBS | 149500000 |
| Vanguard | 2107650000 |
| Northern Inst | 158529000 |
| Western Asset | 395405000 |
| BMO | 14500965 |
| Principal | 2945182 |
| USAA | 25000000 |
- You could define a dynamic range over the pivottable and use the dynamic range as the argument for the LARGE function. Alternatively, why not define a second pivottable tied to the same slicer which shows top 5?
5 Replies
- You could define a dynamic range over the pivottable and use the dynamic range as the argument for the LARGE function. Alternatively, why not define a second pivottable tied to the same slicer which shows top 5?
thanks Jan, I'll try the dynamic range, I can't see how to show only the largest five in a Pivot table, there is nothing in the "value field settings"? Also when if I do a dynamic range I would have to somehow remove the Grand total which is always included at the bottom of the pivot table wouldn't I?
thanks,
Chris
- You should be able to right-click a row or column field and select Filter, top 10.
YOu can account for teh fact whether or not there is a total under the pivottable in your dynamic range name, just as you might want to cater for anythign above or below the PT.
