Applying filter per metric

Dear community

Can someone please let me know, if there’s a possibility to calculate the metrics with the individual filters? I wanna build a combo bar chart with

dimension x = transaction_date

two bar metrics:

  1. sum(amount) where data_type=actuals
  2. sum(amount) where data_type=forecast

and the line which will show sum(amount) where data_type=actuals - sum(amount) where data_type=forecast

The data source is the following:

Many thanks all for help

Hi @AndriyShepel,
You could use a calculated field for each bar metric, something like:
sumOver(ifelse({data_type} = 'actuals', {amount}, NULL), [], PRE_AGG)

sumOver(ifelse({data_type} = 'forecast', {amount}, NULL), [], PRE_AGG)

Then you could use those in a 3rd calc. field for your line field.

Let me know if this works for your case or if you have any additional questions!

Many thanks for this

I’ve tried it but it calculates the same value for all dates (i guess, due to [ ] operator, which calculates the total for the entire data set)
I modified the formula to the following value:

sumOver(ifelse({data_type} = ‘actuals’, amount, NULL), [{transaction_date}, {data_type}], PRE_AGG)

So this gives me what i expect:

I would appreciate if you confirm my understanding that [ ] is used to calculate the total for the whole table. Thanks

Dear Andriy,

What you say is correct. The content of the brackets indicate the aggregation level. In QuickSight we know this concept as a level-aware calculation - aggregate (LAC-A). If there is nothing between the bracket, it will take the total for the whole table. One recommendation I can give is to put your calculated field with the other colums you showed in a table, this can help you understand the different effects of LAC-A and LAC-W. More information on this topic can be found here: https://docs.aws.amazon.com/quicksight/latest/user/level-aware-calculations.html

1 Like