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
- JKPieterseSilver ContributorYou 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?
- Chris SandersCopper Contributor
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
- JKPieterseSilver ContributorYou 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.