Justin
New member
- Joined
- Mar 22, 2011
- Messages
- 13
- Reaction score
- 0
- Points
- 0
Hi all,
I'm working with a PivotTable and having trouble sorting -- I have an idea why it's not working but thought I would check.
Here is a replica of the sheet I'm working with (the data and fields are all fictitious but the structure is the same):
View attachment Pivot_Table_Sorting.xlsx
In rows we have sales reps with a forecast and actual revenue for each. The revenue field appears in the values area three times, calculated in different ways.
The first is a straight-up sum of all revenue for the year, so it appears for both forecast and actual.
The second two fields show the difference from forecast to actual for each sales rep -- so of course these columns only have values for "actual" since the forecast has zero variance from itself.
My challenge comes when I try to sort by either the % Diff. from Forecast or $ Diff. from Forecast fields. The PivotTable just won't let me do it and sorts instead by the Yearly Revenue field.
This happens both when the table is set up with its current structure as uploaded or when I put the Forecast/Actual field above the sales rep field. I've tried all the different ways of accessing the sort menu, but when I select in custom sort to sort sales reps according to % Diff. from Forecast descending, it just defaults to Yearly Revenue again.
My hunch is it won't let me do it because not every row has a value for that column and so it doesn't know how to sort it.
But it's quite inconvenient I think, as it doesn't allow you to easily see which reps have the biggest variance.
Am I missing something?
Thanks for any insight.
I'm working with a PivotTable and having trouble sorting -- I have an idea why it's not working but thought I would check.
Here is a replica of the sheet I'm working with (the data and fields are all fictitious but the structure is the same):
View attachment Pivot_Table_Sorting.xlsx
In rows we have sales reps with a forecast and actual revenue for each. The revenue field appears in the values area three times, calculated in different ways.
The first is a straight-up sum of all revenue for the year, so it appears for both forecast and actual.
The second two fields show the difference from forecast to actual for each sales rep -- so of course these columns only have values for "actual" since the forecast has zero variance from itself.
My challenge comes when I try to sort by either the % Diff. from Forecast or $ Diff. from Forecast fields. The PivotTable just won't let me do it and sorts instead by the Yearly Revenue field.
This happens both when the table is set up with its current structure as uploaded or when I put the Forecast/Actual field above the sales rep field. I've tried all the different ways of accessing the sort menu, but when I select in custom sort to sort sales reps according to % Diff. from Forecast descending, it just defaults to Yearly Revenue again.
My hunch is it won't let me do it because not every row has a value for that column and so it doesn't know how to sort it.
But it's quite inconvenient I think, as it doesn't allow you to easily see which reps have the biggest variance.
Am I missing something?
Thanks for any insight.