Pivot table conditional formatting wrong

Question & Answer
, ,
1

I have a pivot table over multiple rows. On the value delta (statusCountDelta) is a conditional formatting where if the value is greater than 0.1 (10%) it turns green.

As you can see in the screenshot the formatting of the value itself is correct, but in the subtotal the formatting does something wrong in my view.

statusCountDelta is a calculated field: (count - averageOverSumOfCount) / averageOverSumOfCount [if the averageOverSumOfCount is zero → return zero]

Thanks in advance!

screenshot
screenshot1916×935 85.2 KB

2

Hello @ViktorVH, welcome to the QuickSight Community!

Looking at the screenshot you provided, it looks like under value you may have used a comma instead of a period.

3

Hi @Kelseykl, Thankyou for your quick response.

I’ve tried both a period and a comma.
The delta value for March 2024 is also a negative percentage :sweat_smile:

4

Hi @ViktorVH, it looks like the subtotal is taking the average of the values. Is this expected? If not, I would recommend checking that the totals are set to “Default” and not “Average”:
image
Otherwise it may be due to the “averageOverSumOfCount” calculated field.

5

Also, the Delta is expected to be negative for March, right? Because the count is lower than the average count? If not, you could add the abs function to the calculated field to remove the negative:

abs(count - averageOverSumOfCount) / averageOverSumOfCount
6

Yes that is expected. Originally I’ve tried with two conditions (also less than -0.1 → turn RED). But the formatting was exactly the same as now

7

Changing it to default has the following effect.

image
image1555×857 41.4 KB

But I want it to be the average …

8

Hi @ViktorVH,
It’s been awhile since we’ve heard from you. Are you still having issues with the last question you asked or have you been able to resolve it? If so, please provide the solution that worked for you or mark a response above as the solution.

Otherwise, if you still need assistance, please let me know and provide any information about additional tests you have run to work on this.

If we do not hear back from you within 3 days, I will archive this topic.

Thank you!

9

Hi @ViktorVH,
Since we haven’t heard back, I am going to archive this topic. If you have any further questions, feel free to start a new topic for discussion and link this page for relevant information that may be needed.

Thank you!