3 Replies Latest reply on Mar 31, 2017 12:17 PM by Bharatsingh Zala

    Trying to get dynamic deciles based on filters!

    TEG Data Viz-ard

      Hi,

       

      I am trying to make deciles in my data based on 2 metrics; 1. Owner Number, 2. Group Sales.

       

      I would like to 'decile' these owners based on their group sales (descending order). I'm able to achieve this. But when i filter the data, I would like to re-decile based on the filtered data, which is not happening. It still takes takes from the original deciles, based on the complete data.

       

      Steps to form deciles

      Step 1. Created a Calc field -> Group Size = { FIXED [Owner Number] : AVG([Group Size])}

      Step 2. Create another field -

      Decile =

      IF [Group Size]<={FIXED :PERCENTILE([Group Size],.1)} THEN "1"

      ELSEIF [Group Size]<={FIXED :PERCENTILE([Group Size],.2)} THEN "2"

      ELSEIF [Group Size]<={FIXED :PERCENTILE([Group Size],.3)} THEN "3"

      ELSEIF [Group Size]<={FIXED :PERCENTILE([Group Size],.4)} THEN "4"

      ELSEIF [Group Size]<={FIXED :PERCENTILE([Group Size],.5)} THEN "5"

      ELSEIF [Group Size]<={FIXED :PERCENTILE([Group Size],.6)} THEN "6"

      ELSEIF [Group Size]<={FIXED :PERCENTILE([Group Size],.7)} THEN "7"

      ELSEIF [Group Size]<={FIXED :PERCENTILE([Group Size],.8)} THEN "8"

      ELSEIF [Group Size]<={FIXED :PERCENTILE([Group Size],.9)} THEN "9"

      ELSEIF [Group Size]<={FIXED :PERCENTILE([Group Size],1)} THEN "10"

      END

       

      This neatly creates 10 deciles, but on the whole data. Now when i try to filter the data in the sheet, i would like the deciles to recalibrate based on the filters, which i'm facing issue with.

       

      Any help will be appreciated.