Forum Discussion
Solved
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...
- 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.
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.
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?
I think you're right there, you can only show them as a percentage of the total of those 5 items, not as a percentage of everything.