9 Replies Latest reply on Jun 21, 2018 7:21 AM by Zhouyi Zhang

    Top N and Others using a parameters: incorrect results

    Erick NIZARD

      Some background on where I am struggling.


      I have a source data file with the following information:


      • Year
      • Country
      • Client
      • Client Type
      • Revenue.


      I am trying to get the Top N clients for each country and identify their % of the Total Country revenue i.e. Top N vs. ‘Others’


      I have created the following:


      • a Set to group Client based on a ‘Top N’ Customer parameters based on sum(Revenue).
      • A calculated field to display labels (Top N or Others): IF [Top N Clients by Market] THEN "Top " + STR([Top Customers]) + " CIT/Client" ELSE "Others" END
      • A calculated field and a parameters to Expand/Collapse the ‘Other’ bucket: IF [Expand or Collapse] = "Collapse" THEN IF [Top N Clients by Market] THEN [CIT / Client] ELSE "Others" END ELSE [CIT / Client] END



      What I am struggling with … and I tried for a couple of hours:


      1. A subtotal for Top N (i.e. sum the 3 clients in the screenshot above). When I use the subtotal feature, it adds unwanted ones
      2. Add a measure next to the Revenue $ one to display the % of Total. I am able to toggle between the $ and % but cannot have 2 measures side-by-side
      3. Add a viz that display the $ (or %) of Top N vs. Others across all countries (i.e. countries as rows, see pic below). I can add the viz but the aggregation isn’t correct (some countries don’t have a Top N)


      Thanks in advance