1 Reply Latest reply: Mar 27, 2017 3:45 PM by Sunny Talwar RSS

    Top n% in a chart expression

    Austin Kronz

      Hi,

       

      I am trying to insert a reference line into my bar chart for bench marking purposes.

       

      To elaborate, the bar chart uses the measure: SaleAmount,  and dimension: SalesManager. I want the reference line to display as the average of the top 20% of SalesManager. My current expression uses Rank to display the reference line as the average of the top 5 SalesManager, but I need to change it from that to "top 20%".

       

      Here is my exact expression:

       

      avg({<SalesManager={'=Rank(sum(aggr(sum(Sales),SalesAssociate))) <=5'}>}aggr(sum(Sales),SalesManager)

       

      *Due to the structure of my data, I already use the aggr function to aggregate sales by sales associate in the set expression and also by sales manager.

       

      Hope this is clear! Thanks in advance.