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