It didn't work for me!? Let me try and post the updated workbook--I got pretty close with Matthew Hull's suggestion...
First you need to know that whenever you add a dimension to a viz it will change the way T slices and dices the data. So replacing the measure AVG(Average) with a dimension will significantly change your viz, and requires attention to what's being Addressed and what's being Partitioned in your table calcs.
Question: What about the last screenshot you posted "doesn't work"? What order/rank should be happening. Specifically how do you want these first few Brands and ranks to be reordered/reranked?
Is the attached closer to what you are looking for?
There are a number of concepts at play in this analysis, including:
- making your parameter an integer instead of a float
- using a Gantt bar with AVG(1) on the Size shelf
- using a Measure (Discrete or Continuous) pill for Year on the Columns shelf to prevent data densification
- using Year on the Marks card, Details shelf, as a Dimension
- using an Advanced Compute using, with:
1. all dimensions used for addressing, in the order: Patient_ID, Brand_ID, Year
2. Sort by the Min of Year Ascending
3. At the level: Brand_ID (so the Index increments every time the Brand_ID changes, effectively ignoring year for incrementing because of the order of the Dimensions in the Address list box)
4. Restarting every: Patient_ID (so each patient has their own sorting of Brand_ID and own set of Index numbers)
There are a number of advanced concepts in use in the worksheet, you are welcome to get in touch with me if you would like to explore these in greater detail.