I am sure this must have been asked before but I have been unable to find a solution to my problem.
I have a pivot table with 2 dimensions; one dimension is a Drill-Down group.
There are 3 expressions, 2 simple sums and the third is the variance of those 2 (a-b).
One dimension (the non-drill-down dim) is vertically on the left, the other dim goes across the top. There is a totals column on the left.
Dim1 := Store
Dim2(Drill-Down): = Area & Sub-Area
Exp3:= Exp1 – Exp2
They want to sort by the stores with the greates variance between sales and planned sales. I want to sort the 1st dimension (store) by the total column of Exp3. I cannot get it to work. I can sort by Exp1 or Exp2 but not Exp1 – Exp2.
Also if anyone knows a good way of alternating the pivot table dimension BG colour if they could let me know that would be great!
Any tips or pointers on getting this to work or is it not posible?
As far as I know you can only sort on the first expression with the sort by Y-value option. You could promote your third expression to first position. That said, sorting in pivot tables is finicky and rather limited.