Conditionally formatting a pivot table based on total, using conditional aggregate functions

[raul_23]

Hello,

I’m doing analysis on favorable survey response rates with a dataset similar to the first screenshot below, where each row represents a single response. Each response also contains the country/region of the participant.

I have a calculated field to measure the favorable response score (number of favorable responses divided by total number of responses that provided an answer in Satisfaction)

countIf(response_id, Satisfaction = 4 OR Satisfaction = 5)
/
countIf(response_id, isNotNull(Satisfaction))

Using a pivot table, I want to conditionally format the countries where the favorable score is equal or greater than the total/overall score. In the screenshot below, conditional formatting should apply to USA and MEX as they are higher/equal to the Total (66.7%).

I tried creating another calculated field that uses a LAC-A function to measure the overall score using the Region column as the dimension, and then create another calculated field to measure the difference and identify the countries with a higher or equal score. However, this is not possible in my use case as LAC-A functions are not supported for conditional aggregate functions (countIf() for example) according to the AWS documentation.

Below is a hardcoded mock-up of what I’m trying to achieve.

Is there another workaround to this LAC-A limitation without altering the dataset?

Thanks in advance!

[Jesse]

@raul_23 You are definitely on the right track with using the LAC-A functions! Had to play around with this one for a little bit but think I got it. In my version, I am using the condition of Segment=SMB in place of your Satisfaction=4 or 5, but concept should still work.
Percent Favorable:

Percent Favorable Total:

Comparison:

Then using the Comparison field, you can do the conditional formatting:

Hope this helps!