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.

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

Anyone? I am struggling with this one

• ###### 2. Re: Top and Bottom 20 % within one measure in table

You can refer my website for this

http://www.tableaulearners.com/2016/top-n-and-bottom-n-view-on-the-same-worksheet/

Warm Regards,

Rajeev Pandey

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

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

Hi Jim,

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