I want to sort the employees in this table (whose names are crossed out in red) using only the value in the $Work$ row.
So the employee with highest $Work$ would be listed first (along with his/her Work, Hours and CPH), and then the employee with the second-highest $Work$, etc.
I remember having done this by defining the sort…but can’t find it now.
Thanks in advance!
Hi,
You can return the measure values just based on hierarchy value = “Work”. Next, rank them according to the new calculated field values so that the other measure values corresponding to CPH and Hours are not ranked. And then the table can be sorted based on the rank measure.
Something like below, where I calculated Work_numbers with if ($Measure$==“Work”){return $Val$;} and created rank with RANK($Work numbers$). These two can be hidden later.
Upasana,
Unfortunately Work, Hours and CPH are separate formulas so I can’t use the formula you suggest in order to create a column containing just the Work value. I was able to get the Rank of the work, and the ranking in the grand total column appears to be correct. What I can’t figure out is how to sort the Associate names based on the new “Rank Work Expression Expression” formula.
I think the issue is that I want to sort a heirarchy (“Assoc + Username”) by a measure (“Rank Work Expression Expression”). The Dundas support page says this is possible, but I’m not seeing the measures on the configure window for “Assoc + Username”:
https://www.dundas.com/support/learning/documentation/analyze-data/define-custom-hierarchy-sorting
Also, the (All) column to the right of the Grand Total column is unneeded and I can’t figure out how to make it go away. The (All) next to Monday, 1 Nov is the total for the week, but the (All) next to the Grand Total is just another copy of the Grand Total. I only need one. 
Ok, we figured it out:
The trick was to go to Work in the data analysis panel and select “custom measure sorting”, then have it sort Associate + Username:
We got the extra “All” subtotal to go away by setting one of the calendar hierarchies to show only “subtotal” rather than “grand total and subtotals”
Now I still have the puzzle as to why the CPH cell sometimes is colored correctly (criteria is 16 and above to be blue) and sometimes not.
