5 Replies Latest reply on Jun 28, 2012 7:36 AM by Maria Fernandez

# Rank as first row on cross tab

I understand that the logic behind Tableau data engine may not allow something like this.

 Rank % Product Component Sold 1 26.32% Product 1 Component 1 20 2 19.74% Product 2 Component 2 15 3 19.74% Product 3 Component 3 15 4 11.84% Product 4 Component 4 9 5 11.84% Product 5 Component 5 9 6 5.26% Product 6 Component 6 4 7 1.32% Product 7 Component 7 1 8 1.32% Product 8 Component 8 1 9 1.32% Product 9 Component 9 1 10 1.32% Product 10 Component 10 1

Any ideas for getting similar results.

Thanks

M.

• ###### 1. Re: Rank as first row on cross tab

Hey Maria,

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.

-David

• ###### 2. Re: Rank as first row on cross tab

Hi David,

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?

Thanks,

M

• ###### 3. Re: Rank as first row on cross tab

Hey Maria,

It was a bit of a convoluted process, but I got it to work (Attached).

Issue:

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.

-David

• ###### 4. Re: Rank as first row on cross tab

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.

Jonathan

• ###### 5. Re: Rank as first row on cross tab

Sorry for the 3 months delay replying, but just to let you know your solution worked perfectly.

Thanks,

Maria