4 Replies Latest reply on Aug 2, 2017 12:09 PM by ukasz.gierada.0

    Top and Bottom 20 % within one measure in table

    ukasz.gierada.0

      Hi Guys,

       

      so the problem that I am facing currently is related to simple table

      I have 10 different measures, some are integer, some percentages.

      Within each individual measure within one dimension ( in example it would be Segment - Consumer ) I would like to Color top 20 % of Customer name and Bottom 20 %

       

      Below this is final view - 2 of 10 values within each measure are colored to Green and 2 are colored to red.

       

      Segment
      Customer Name
      SalesProfit
      ConsumerAaron1230
      John6612412
      Mark51
      ..12044
      ..15066
      ..18077
      ..288
      ..199
      ..222443
      ..483

       

       

      I was trying to use Index but then I would need to do this for each of the columns?

        • 1. Re: Top and Bottom 20 % within one measure in table
          ukasz.gierada.0

          Anyone? I am struggling with this one  

          • 3. Re: Top and Bottom 20 % within one measure in table
            Jim Dehner

            Hi

            a 2 part answer - first determining percentile ranking for the different measures is straight forward

            First for profit =     RANK_PERCENTILE(sum([Profit]),'desc')

            and for sales=     RANK_PERCENTILE(sum([Sales]),'desc')

             

            Then applying color there are 2 matching calculations

            for profit      if [using percentils on profit] > .8 then 'Green' elseif [using percentils on profit]<.2 then 'Red' end

            Then on Sales      if [using percentils on sales] > .8 then 'Green' elseif [using percentils on sales]<.2 then 'Red' end

             

            Then for each you need to edit the color tile:

             

             

             

             

            OK that's the straight forward part

            If you just make a viz and  and drag the profit sales and percentiles to the viz and then color one of the color calculations all the columns will have the same color coding

            That's not what you wanted

             

            There is not a real easy way to color code the columns differently but there is a HACK that gets you real close ( Shin - I used one of your previous replies on this)

            You create a dummy field for each of the columns you want as shown below - setting the value to 0 creates and empty set

             

            Then you drag each of the "Empty Sets" to the columns shelf

            and it will create 4 separate marks cards

            Open each marks card separately and place the values measure (ie profit percent) on detail and the color coding on the color tile (see below

             

             

            Unfortunately the headers are at the bottom of the chart - haven't figured out a way to undo that -

             

            What you have are separate color coding - top and bottom 20% and the middle is just set to blue

             

            This was a real interesting problem - thanks for the opportunity

             

            Jim

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            • 4. Re: Top and Bottom 20 % within one measure in table
              ukasz.gierada.0

              Hi Jim,

               

              I will back to you once I will test it. But nevertheless thank you for you time