Expression Help

[satishkurra]

Hi

I have the below pivot table data with Country, Service Lines and Sales%

Country		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19
A		40.0%	12.0%	6.0%	1.0%	-5.0%	12.0%	5.0%	-1.0%	6.0%	13.0%	-40.0%	35.0%	145.0%	9.5%	11.1%	-18.0%	245.2%	16.3%	-69.5%
B		1.0%	1.0%	3.0%	56.0%	45.0%	23.0%	8.0%	10.0%	6.0%	56.0%	34.0%	-10.0%	-20.0%	-30.0%	37.4%	90.0%	8.0%	6.0%	-70.0%

I need to show Increase Icon for highest % and nothing for other %

For example, for country A, highest % is 145% which should show icon and the rest all values for Country A should be blank.

I understand that we can do a custom format cells, but the value 145% is not constant and once data changes the highest % may go to other service line.

Request to please help on the same.

Thanks

Satish

[satishkurra]

Sorry my bad, the highest % is 245% for Country A....

[sunny_talwar]

Couple of questions:

1) Why do we need custom format cell to color a cell? Why not background color with condition

2) Isn't 245.2% is the highest (under 17) for Country A

[satishkurra]

Hi Sunny

You are correct. We need to show 245% with an icon and the rest as blank

Ideally we only show 1 value for each country

[sunny_talwar]

So you just wanted to see Country and next to it the highest value?

Dimension:

Country

Expression:

Max(Aggr(Expression, Country, [Service Lines]))

[satishkurra]

Don't we need service line also as dimension 

[sunny_talwar]

I guess it depends on what you want to see? Do you want to see all service lines or just the max?

[satishkurra]

All Service Lines

I'm trying to get this in below fashion. Only highest % should be displayed and rest all empty/blank

Country		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19
A																		245.2%
B																	90.0%

Thanks

Satish

[sunny_talwar · Accepted solution]

How about this:

If(Sales % = max(Total <Country> Sales %), sales %, Dual(' ', 0.0001))

Or this if sales % is an expression:

If(sales % expression = max(Total <Country> Aggr(sales % expression, Country, service line)), sales % expression, Dual(' ', 0.0001))

[satishkurra]

Hi Sunny

With the above expression, it is showing 0.0% for other than Max Value, Can we simply show blank instead of 0.0%?