Conditional formatting not working when filtering a table

Hello
in my dashboard I have 2 tables: a main table that, given the names, shows the main metrics (M1 to M8 in the screenshot below) and a secondary table that, for each name, shows main + additional metrics (M4, M1, M2, M31 and M3 below). The tables are filtered by Dataset Day with a top1 filter and all the rest of the filters you can see in the screenshot.

In both tables, conditional formatting is applied to the metrics based on the value of percentileRank

Example of calculations for M1 (all the others are similar):

1. percRank_m1 = percentileRank([({m1}) ASC],[],PRE_AGG)
2. association of an integer value to the percentile Rank:

color_m1 = ifelse( sum({percRank_m1})<2,-2, sum({percRank_m1})<5,-1, sum({percRank_m1})>98,2, sum({percRank_m1})>95,1, 0)

3. apply a conditional formatting to M1 in the table based on color_m1: 
-2 = green
-1 = light green
1 = orange
2 = red

Problem

  1. The main table is used to filter the second table (via actions).
    When it happens, the colors for the second tables are completely wrong as, I suppose, the dataset is restricted to a single row and - hence - the calculations only take into account that row.

  2. I experience the same when trying to filter the main table using a “name” filter. It reduces the dataset to the rows I select and, once again, the colours are wrong.

Would you have any suggestion to solve this? Using PRE_FILTER doesn’t seem an option as my dataset would not consider a single dataset date but all the dataset dates available and, hence, the percentileRank would be wrong.

Thank you so much for your help on this!

As I’m a new user, I’m not allowed to post more than 1 image into the post.

Adding a second screenshot to clarify the problem #1 above.

You can see how M1 (197) is red into the main table while it’s green in the secondary table.

Welcome @damarino
can you show the conditional formating for both tables?
BR

This is the conditional formatting used for each metric (in both main and secondary table).

And here the color definition, as in #2 in the example above

Screenshot 2023-10-26 at 17.33.20

can you change the aggregation to sum?
grafik

2 Likes

Hello Erik,
sorry for my late reply. Unfortunately only “Custom” aggregation is available in the dropdown menu.

Hi @damarino ,
can put percRank_maxContract% and Color_CotractRate in to a table visual and check?
I assume it is due to the aggregation
BR

1 Like

Hello @ErikG,
I have added both percRank_maxContract% and Color_CotractRate to the primary and secondary table.

As you can see in the primary table 58 is red (as percRank>=95 and, hence, color_ContactRate=2).
In the secondary table these values are completely different.

I have tried different aggregation for percRank_maxContract% and Color_CotractRate (SUM, MAX, MIN) in both primary and secondary table but the numbers stay unchanged.

cc: @Max @duncan @Kristin @ErikG

Hi Davide @damarino ,

Since PercentileRank is in play, it is very much dependent on your data.
(Say for example, you might have only one record per country per region for a single day
vs Sample data that we use to mimic your problem might have more records per day.
Such differences will cause differences in the end result.)

So, Please create a sample dashboard showing your problem state using Arena and post it here.
(Details on using Arena can be found here - QuickSight Arena )
We will take a look once we have the sample from you.

Regards,
Arun Santhosh
Principal QuickSight SA

1 Like

Hello @ArunSanthosh,
thank you for your reply and sorry for my late reply!

I have replicated my dashboard using the Arena with fake data.
All the formulas used are as in the original dashboard.

Conditional Formatting & Filtering

Thank you for your help on this!
Do not hesitate for any question or doubt!

Best regards,
Davide

Hi Davide @damarino ,

You were running into issue with conditional formatting on second table as the filter action was eliminating records from it.

One solution is to use a parameter paired with a navigation action and Top N filter to cut the data being displayed down to what you want, while keeping the records in play for the percRank calculation. This works since TopN filters are evaluated after aggregations at visual level.

Here’s the dashboard with the solution: Q20422 Solution

Elements to check out are listed below.

Parameter: pName
Calculations: NameMatchesParameter?. NameParameterSetValue
Action: Set pName Value
Conditional Formatting: Entire Row on Table 1.

The colors are matching across tables as can be seen below.

Note - I wouldn’t sum the percRank values ideally but leaving that untouched as it might be working for you based on your data.

Regards,
Arun Santhosh
Principal QuickSight SA.

1 Like