Do you have the sample workbook?
I have the same questions. Need to show top or bottom 5 by YOY, but when ranking a table calc, the rank is all out of sorts.
Besides manual and alphanumeric sorts, we can only sort dimensions (from primary sources) based on regular aggregate measures from the primary source, so that excludes table calculations. To get a table calculation to sort we need to do one of the following:
a) Set up a table calculation that returns the desired alphanumeric sort value for each discrete header pill that needs sorted. In the case of a sort descending on Sales, that means the negative of sales.
b) Change the table calculation into a regular aggregate measure, for example by using LOD expressions.
c) Change the table calculation into a blended aggregate where a self-blend is used to help get the equivalent table calculation result and then the blended aggregate is set up to return the desired alphanumeric sort.
In the attached I set up a) table calc and b) regular aggregate. For a) I used a second table calculation that returns the -WINDOW_MAX([YoY Sales TC]) for every State, then put that pill to the left of the State pill in the view:
For b) I used a Level of Detail Expression to get the latest year in the data returned as a record-level results. Then the Current Year Sales is IF YEAR([Date]) = [Latest Year] THEN [Sales] END and the Prior Year Sales is a variation on that. Finally the YoY Sales measure is ZN(SUM([Current Year Sales]))-ZN(SUM([Prior Year Sales])) and that can be directly used as a measure and sort the State dimension:
sorting YoY.twbx 86.3 KB
Thank you Jonathan, option B worked great.
On Thu, Feb 4, 2016 at 5:12 PM, Melissa Davenport <