How do i choose which way the conditional format happens on a pivot table?


I’m trying to add conditional formatting to a pivot table, but when I use it on the numbers that I have it doesn’t allow you to choose which way to ‘Calculate’ the formating. I would like it conditionally formatted looking at the previous year for that month. In other words comparing this month to what it was YoY.

I have managed to get around it by using the same field but adding ‘Table Calculation’ to it but this then gives me another row rather than adding the icon to the left of the original ‘number’.


@e2s, you can create a new calculated field as YoY percent difference/difference for consumption measure and then use this newly calculated field to conditionally format the consumption measure column in your pivot table. Here is an example on how to calculate the yoy percent difference measure - periodOverPeriodPercentDifference(sum(Sales), {Order Date}, YEAR, 1).

I am using a mom% measure to conditionally format the sales sum in the below pivot table screenshot, I have hidden the mom% so that it is not visible in pivot table.

If this helps in addressing your use case scenario, please help the community out by marking this answer as “Solution!”


1 Like

Hi @DeepakS ,

I might be missing something here,

I have set this up but not able to see the results, are you able to please elaborate on how you’re doing the conditional formatting? on what ‘Value Field’?

I also see in your values with no ‘Arrows’ up or down in your table e.g. Costa Rica , Dec 2021…, is that normal?

In the attached you can see conflicting conditions. ‘Consumption Icon’ has a ‘Table calculation’ associated with it as a ‘percentage difference’ and calculate as ‘Table Down’.

Thank you

Hi @DeepakS ,

That works now, thanks but not too sure what I did. I had to go out of the analysis and back into it. I also ‘Showed’ the columns that I had ‘hidden’ in my case “Month Number” and re-‘Hid’ them again and for some reason it’s working now.

I also changed the “Consumption % YoY difference” ‘show as’ → Percent ( not sure if that helped but it was 1 of my steps)

I set up my Condition on the new Calculated Field “Consumption % YoY difference” as attached and then hid that field.

Thanks for the solution! Spent hours and hours trying to crack it. :grinning: