I have gone through a number of threads and found some wonderful answers but did not come across something exactly fitting my situation.
I have two pivot charts. Both charts have cyclic groups, although this issue may not be directly related to cyclic groups.
Chart1 shows Actual revenue for Parent and child facilities (cyclic group 1 parent many children) for the current year.
I went to Group Sort Order under Setting --> Document Properties and specified to sort according to descending value of Actuals for both Parent and Child.
Worked fine for Parent but for Child Facilities it shows a few facilities (15-20) with 0 Actuals on top but after that shows correctly in decreasing order of revenue for the rest of records. I went into the sort tab for the chart and specified Sort By Y-value Descending and it started working correctly for child facilities.
Chart 2 however shows exact same information and cyclic groups but for 2 previous years, current year and 2 future years (Actuals replaced by Backlog for future years) shown in that order (e.g. 2011 2012 2013 2014 2015). If 2013 was the first column there would be no issue but 2011 is the first column and sorting by Y value sorts according to 2011. Sorting by descending value of Actuals for the current year displays the same wierd behavior for child facilities i.e. (it shows a few facilities (15-20) with 0 Actuals on top but after that shows correctly in decreasing order of revenue for the rest of records).
So, my question is
a. Can I sort by Y value and specify my 3rd column to be the target of the sort?
b. If not, has anyone seen this weird behavior where 0 value rows show up at the top?
I have a separate calculation for each year actuals. I have tried unchecking and checking Override Group Sort Order.
Any idea to proceed further will be a great help.