Highlight Cells by average of each column in Pivot table

Question & Answer
,
1

Hi,

Im currently struggling with this,have tried multiple formulas with LAC,conditional formatting with 1&0 but None seem to work for me.

So i have a pivot table having columns say ProductCategory as vertical and Region as Horizontal.
It displays the sum of Profit in the table.

Now i need to highlight cells green if they are greater than average value of sum of Sales by each Region. Attached sample dataset example

image
image1188×390 56.3 KB

Added AvgProfit calculated field for reference,its formula : avgOver(sum(Profit),[Product Category,Region])

2

Try this.

ifelse(sum({arrival_timestamp_int}) >avgOver(sum({arrival_timestamp_int}),[{client_id}]),'highlight','do not')

Then conditionally format it so when this field is ‘highlight’ you highlight it.

2 Likes
3

Hi Max.
Thank You for the help

Well it actually worked.

I was the using the same formula in my previous attempts but in LAC dimensions i was passing both ProductCategory and Region.Passing only Region gave the correct calculation.