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

    Top and Bottom 20 % within one measure in table


      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.


      Customer Name



      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

          Anyone? I am struggling with this one  

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


            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



            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

              Hi Jim,


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