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
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.
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.
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.
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.
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.
