Background Color for Dimension that ignores a second dimension

[#1]

Hi all,

I am creating a pivot table that shows a person's rank per competition like this screenshot.

The person column needs to get a background color, green if a Person has a Rank 1 over all Competitions and red otherwise. I have used the following code to do this:  if(min(Rank)=1,RGB(0,250,0),RGB(250,0,0))

However, Person B gets the color red, while he has a Rank 1 in Competition Y. So my guess is that my expression only looks at the first column after Person (i.e. looks at only the first Competition).

Does anyone know how I can adjust my expression to check all Competitions for a Rank 1 and change the Person's background to green? The file is attached.

Thanks in advance!

Vincent

[whiteline · Accepted solution]

Hi.

Use total to calculate min for each person:

=if(min(total<Person> Rank)=1,RGB(0,250,0),RGB(250,0,0))

[#3]

That works, but i still have a question. If I have more competitions and not all persons have participated to all of these competitions, like this screenshot: 

How can I give colors to Person A and Person D?

[whiteline]

Could you provide an example ?

[#5]

This is the file with the example above.

[whiteline]

Sorry I've forget.

It's an issue with colorizing.

When the first cell in row is null() the color is discarded.

(to check and see it just set 'sort by number' for Competition)

The only way is to have some values there.

[#7]

Thanks this works. I have changed the Null values to 'Not Available' and the background is now green.