Forum Discussion
Chris Sanders
May 24, 2018Copper Contributor
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 sta...
- May 29, 2018You 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?
JKPieterse
May 29, 2018Silver Contributor
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?
- Chris SandersMay 29, 2018Copper 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
- JKPieterseMay 29, 2018Silver 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.- Chris SandersMay 29, 2018Copper Contributor
thanks Jan, I think the dynamic range might work better, I'll give it a go. The top 10 sort then just shows the top 5 values which is good but I then want to turn them into a chart showing the top 5 values as a percentage of the total so I can't do that with just the one pivot table can I?