Create a dinamic difference

Hi all,
I would like to create a sort of comparator in quicksight.
The user should be able to pick a baseline group and a comparison group by selecting the features of each group from some controls inserted in the page (see picture attached).

The only solution I found so far is to define two calculated field base and comparison as:

comparison ifelse(in({trans_cpp_size_category}, ${sizeBase}),“base”,“null”)
comparison ifelse(in({trans_cpp_size_category}, ${sizeComparison}),“comparison”,“null”)
by using two different parameters sizeBase and sizeComparison.

Question 1.
Is there a way to create a unique field “grouping” which can be base or comparison, able to handle the situation in cui the groups are the same?

i.e this solution is not working great

ifelse(in({trans_cpp_size_category}, ${sizeBase}),“base”,
in({trans_cpp_size_category}, ${sizeComparison}),“comparison”,

in case sizeBase and sizeComparison are both Small, I’m not entering in the else part of the conditional expression, therefore comparison will be null.

Question 2.
For realizing the diff column I would like to avoid to manually doing the diff of each metric


In this way I’m getting the difference between base and comparison but I would need to potentially create 40-50 different calculated field for each metric…

Happy to clarify further if someone can help :slight_smile:

Hi @mattraga ,

For Question 2 , you can take a look at dynamic measures :

For Question 1 : I am not entirely sure . Would be helpful if you could put a sample data and the expected output.
an example post where sample was provided in table format ( Help with Rank function
Help with Complex Calc!