2 Replies Latest reply on Jul 10, 2014 9:52 AM by manoj.kasam

    ranking dynamically by different dimensions



      I need to create a table showing only top N records (dynamic) on a kpi based on different dimensions....for example

      if there is Profit measure which is pivoted by dimensions city,product name and customer name.

      I want this table to have ability to select top N records with highest profit by different dimensions

      I think this can be done by creating two diff parameters one for Top N and one more parameter for dimensions where the user can select dimensions..

      but not sure how can I link these two parameters..


      any suggestion is appreciated..

        • 1. Re: ranking dynamically by different dimensions
          Ben Neville

          You attached a .twb which does not contain the data, so we can't see exactly what you're doing in this workbook. What you could do is have a parameter that has different dimensional values. Let's call it Dimension Parameter.


          Dimension Parameter holds the following values:


          Dimension 1

          Dimension 2

          Dimension 3

          Dimension 4


          Rank Parameter will integer values so your user can decide the top X values. Just make it an integer with no min or max (or enforce a min or max if you want).


          You then need a calculation. We'll call it Dimension Calc. It will look something like the following:


          CASE [Dimension Parameter]

          WHEN "Dimension 1" THEN [Dimension 1]

          WHEN "Dimension 2" THEN [Dimension 2]

          WHEN "Dimension 3" THEN [Dimension 3]

          WHEN "Dimension 4" THEN [Dimension 4]



          Place Dimension Parameter on your filter shelf, and choose the Top filter option. For your top number, click the dropdown and choose your Rank Parameter. Base this on whatever measure you want.


          You can also place Dimension Calc on your rows/columns shelf or somewhere on your sheet to dynamically display new dimensions based upon user selection.

          • 2. Re: ranking dynamically by different dimensions

            Thank You Ben it worked.