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