6 Replies Latest reply on Jul 31, 2018 8:54 AM by Ankit Bansal

    How to dynamically show Top 4 items in the same table?

    Yuehan Guo



      I'm having trouble creating a single sheet item that can show the top 4 compounds within a select TA area, and group the other compounds within the same TA area into "Others".


      There are 2 TA areas: Oncology and Urology

      Each area has its own compounds which are assigned different budget numbers.

      A compound can belong to one single TA area.


      TA area is a dimension field in the data set, and it's used as a parameter.


      Here's what I want to achieve: when Oncology is selected, I should get View 1; when Urology is selected, I should get View 2 from the same table


      Table View 1

      Top4 CompoundBudget FY
      Oncology Compound Name 1500
      Oncology Compound Name 2400
      Oncology Compound Name 3300
      Oncology Compound Name 4200
      Others (Note: this includes the rest of oncology compounds)100
      Grand Total1500

      Table View 2

      Top4 CompoundBudget FY
      Urology Compound Name 1400
      Urology Compound Name 2300
      Urology Compound Name 3200
      Urology Compound Name 4100
      Others (Note: this includes the rest of Urology compounds)500
      Grand Total1500


      Current status: The Top4 Set looks through the entire data set to find the top 4 compounds, not within each TA area. So if I select on Oncology, I get this.

      Techniques used:

      1. created 2 calculated measure fields, each includes only the budget for Oncology or Urology

      2. created 2 sets, each for every TA area, selected Top 4 by "measure name" sum

      3. created 1 calculated dimension field:

      If [Para_Ta] = 'Oncology' then

          if [Top4 by Measure1] then [Compound] else 'Others' end

      elseif [Para_Ta] = 'Urology' then

          if [Top4 by Measure2] then [Compound] else 'Others' end

      ELSE 'Others'



      Top4 CompoundBudget FY
      Urology Compound Name 1500
      Urology Compound Name 2400
      Othersdidn't validate what this number includes....
      Grand Total


      Any help is appreciated!