Colour Coding based on Ranking

[#1]

Dear Users,

I am having tenders from different vendors for different projects. I could able to do max and min. Requested to help me for rest. for reference please find the attached application.

Thanks and regards

Debdutta

[squeakie_pig]

Can you provide more info on what you are trying to do?  Do you only want the min and max have a different cell colour?

[deec]

It looks like he wants to find the rank of total across each row.
He color coded the background of each measure to show if it is less than all the other measures. In other words, he's highlighting the minimum. He can easily flip this to the maximum.

But he's trying to find out, for each dimension value (city), which is the largest measure, second largest, etc... and use that to color code

I'm thinking about this myself and it's not very trivial.
An similar solution would be to divide each measure by the row total. This would give you a percent of total. These numbers will give you a gradient coloring. But not a ranked coloring.

I'll keep looking, but to me, this is a data modeling issue. The model has split "roadways" (in my view a single measure) into "Mata roadways", "Punjab roadways", etc...
And now we're trying to rank which MEASURE is greatest across a dimension. Rather than which VALUE is greatest across a dimension (where the rank function could help)

I'd love to know if there is a way to rank measures. Haven't ran into this one myself yet.

[squeakie_pig]

You could build a condition in the Background Colour using RangeMin and RangeMax functions

eg. =RangeMin([MATA ROADWAYS],[PUNJAB  ROADWAYS], [DURGA ROADWAYS], [MOHAN ROADWAYS], [INTER  ROADWAYS], [RSNT  ROADWAYS])

Personally I would change the data model as well so instead of each Roadways being a separate metric, you have one column 'Roadways' with the amount and another column (dimension) to identify the roadway type.

LOAD [Sr No],

     Dist_Name,

     City_Code,

     City_Name AS cITYnAME,

     Volume,

     MATA as 'Roadway Amt',

     'MATA' as 'Roadway Type'

FROM

(ooxml, embedded labels, table is Freight);

Concatenate

LOAD [Sr No],

     Dist_Name,

     City_Code,

     City_Name AS cITYnAME,

     Volume,

     Punjab as 'Roadway Amt',

     'Punjab' as 'Roadway Type'

FROM

(ooxml, embedded labels, table is Freight);

[ciaran_mcgowan]

Hi Debdutta,

I'm not sure if this is what you are looking for but if it's just a MIN and MAX, please see the attached document. You were almost there so I just added in the MAX. I know it looks like I've just coloured one of the column backgrounds but that's because MOHAN ROADWAYS is consistently the highest bidder.

[#6]

I want to do L1, L2 .....Ln in different colour code in each row.

Regards.....