I have a dataset like below. My quicksight dashboard has control on site column. How can I create a report to show the #1 site based on the rank of the group that site belongs to? for example, if I select site CCC, the report should display site AAA which is the #1 site in Group A (Site CCC belongs to group A)
Site |
Group |
Rank |
AAA |
Group A |
1 |
BBB |
Group A |
2 |
CCC |
Group A |
3 |
DDD |
Group B |
1 |
EEE |
Group B |
2 |
FFF |
Group C |
1 |
GGG |
Group C |
2 |
HHH |
Group C |
3 |
III |
Group C |
4 |

- define a parameter called SiteParam and add parameter control to the analysis
- add calculated field called SiteOrNull with expression
ifelse(Site=${SiteParam}, Site, NULL)
- add calculated field called minRank with expression
minOver(ifelse(isNull(SiteOrNull), NULL, Rank), [Group], PRE_AGG)
- add calculated field called minRank2 with expression
minOver(ifelse(isNull(minRank), NULL, Rank), [Group], PRE_AGG)
- add calculated field called toShow with expression
ifelse(Rank=minRank2, ‘Show’, ‘Hide’)
at this point in time it should look similar to
Now add a filter on toShow column to remove rows with “Hide” value
3 Likes
Another solution with top/bottom filter:
- Create a parameter called “Site” and add it as control. (Site control get value from site column of dataset).
- Create a field called “isSite” with expression: “distinct_countIf(Site, Site=${Site})”. Now, the data looks like:
- Add a filter:
- Add another filter:
1 Like
Thank you so much. I will try this tomorrow and update here.
Thank you so much. I will try this one too and update here.