2 Replies Latest reply on Oct 29, 2018 8:15 AM by Marcia Miller

# Tableau 2018.2 Rank Measures by Parameter and Display Top or Bottom N

Hi all.... I'm trying to create a view of a cross tab with 4 table calculations.  I'd like the user to be able to select the measure to rank, and then show the Top N of that measure.  I have created the parameters, but I'm having problems with the calculations to control the parameters.  I'm using this formula to rank the measures selected in the parameter.  I've also attached a packaged workbook.  I'm trying to figure out why the formula is not ranking correctly and then figure out how to display the Top or Bottom N.   Any help would be greatly appreciated.

CASE [Rank by]

WHEN 'Profit Diff' THEN RANK(ZN(SUM([Profit])) - LOOKUP(ZN(SUM([Profit])), -1), 'DESC')

WHEN 'Profit Diff %' THEN RANK((ZN(SUM([Profit])) - LOOKUP(ZN(SUM([Profit])), -1)) / ABS(LOOKUP(ZN(SUM([Profit])), -1)), 'DESC')

WHEN 'Quantity Diff' THEN RANK_DENSE(ZN(SUM([Quantity])) - LOOKUP(ZN(SUM([Quantity])), -1), 'DESC')

WHEN 'Quantity Diff %' THEN RANK((ZN(SUM([Quantity])) - LOOKUP(ZN(SUM([Quantity])), -1)) / ABS(LOOKUP(ZN(SUM([Quantity])), -1)), 'DESC')

END

Ideally ... this is what I'd like to accomplish.

• ###### 1. Re: Tableau 2018.2 Rank Measures by Parameter and Display Top or Bottom N

Marcia,

Here's a different approach, not sure if it will work for your true setup.

I first converted all the quick table calculations into calculated fields

by dragging them (each separately) onto the Measure Pane.

This created a Calculation1 field which I renamed.

Then I made a [SortValue] field based off the parameter

- ( CASE [Rank by]

WHEN "Profit Diff" THEN [Profit Diff 1]

WHEN "Profit Diff %" THEN [Profit Diff 2]

WHEN "Quantity Diff" THEN [Quantity Diff 1]

WHEN "Quantity Diff %" THEN [Quantity Diff 2]

END

)  //  note the negative sign used to sort descending

This I placed on the Row Shelf as a discrete pill to the left of [Sub-Category]

so it forces the sort.

For the Top N part, I made a calculated field:

INDEX()<=[Choose Top N]

and placed that on the Filter Shelf and

set it's compute using to be [Sub-Category]