I would use the index function in this case. It fits the use case to a "T".
Step 1 - Create a Function Called "Rank" with the formula "Index()"
Step 2 - Drag the "Rank" Calculation to you Columns Shelf
Step 3 - Change the Compute Using via the Pill Menu to the '%' column
That should get you what you are looking for.
Thanks for your quick reply.
I have followed steps 1-2, but % does not appear as option for computing on the pill menu.
Could use the following sample to show me how?
CrossTab Rank.twbx.zip 39.6 KB
It was a bit of a convoluted process, but I got it to work (Attached).
We not use a Table Calc as a Compute Using for a new Table Calc.
Resolution (Case Specific):
In order to get this to work I first had to add a Percent column to the raw dataset, this is because there we no measures in the dataset other than what Tableau calculated. Second, I added an advanced Table calc for the Rank function that ranked by the Sum of Percent for Product and Component Descending. Then in order to get the sort to function, I had to create a Set for Product and Component.
117065.CrossTab Rank.twbx.zip 83.8 KB
There's a way to do this that doesn't require adding a column:
1. Create a set on Product, Component.
2. Drag the set as the first item on the Rows shelf.
3. Click on the set's pill on the Rows shelf and set the Sort of the set to Descending, Sum of Number of Records.
4. Click on the set's pill on the Rows shelf and uncheck "Show Header". This hides the set while we retain the order.
The table calculations can both be left at Table (Down) and will still work.
Now, to get the columns in the order that were shown:
1. Drag the Rank measure onto the Rows shelf.
2. Click on the green Rank pill and set it to Discrete. The pill now turns blue.
3. Drag the blue Rank pill over to the left, just after the set pill.
4. Repeat steps 1-3 for the % measure.
5. Showing the last column can be a little messy. You can use the steps 1-3 above, or you can put SUM(Number of Records) on the Text Shelf (but not have a header), or leave Measure Names on the columns shelf, drag Number of Records onto the Text shelf, and then set the alias for No Measure Value to "Number of Records".
Sample workbook is attached, though it's a 7.0 workbook.
CrossTab Rank jtd edit.twbx.zip 39.6 KB
Sorry for the 3 months delay replying, but just to let you know your solution worked perfectly.