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

    Marcia Miller

      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.