# Applying filter per metric

**URL:** <https://community.amazonquick.com/t/applying-filter-per-metric/40265>\
**Category:** Q&A\
**Tags:** dashboard-embed, developer, quick-sight\
**Created:** [December 20, 2024, 7:03pm UTC](https://community.amazonquick.com/t/applying-filter-per-metric/40265 "2024-12-20T19:03:57Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![AndriyShepel](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/andriyshepel/32/29211_2.png) [@AndriyShepel](https://community.amazonquick.com/u/AndriyShepel)\
**Post date:** [December 20, 2024, 7:03pm UTC](https://community.amazonquick.com/t/applying-filter-per-metric/40265/1 "2024-12-20T19:03:58Z")

</div>

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:

 ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/7/d/7d296620971509a524596e63095d34712f0746c0.png)

Many thanks all for help

---

<div class="post-metadata">

**Author:** ![Brett](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/brett/32/36494_2.png) [@Brett](https://community.amazonquick.com/u/Brett)\
**Post date:** [December 20, 2024, 10:07pm UTC](https://community.amazonquick.com/t/applying-filter-per-metric/40265/2 "2024-12-20T22:07:40Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![AndriyShepel](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/andriyshepel/32/29211_2.png) [@AndriyShepel](https://community.amazonquick.com/u/AndriyShepel)\
**Post date:** [December 21, 2024, 10:04am UTC](https://community.amazonquick.com/t/applying-filter-per-metric/40265/3 "2024-12-21T10:04:18Z")

</div>

> [@Brett](#):
>
> sumOver(ifelse({data\_type} = ‘actuals’, {amount}, NULL), , PRE\_AGG)

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:

 ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/6/8/680737f64a1af35cefd54c10d01fb890dd950473.jpeg)

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

---

<div class="post-metadata">

**Author:** ![brancofrancois](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/brancofrancois/32/13290_2.png) [@brancofrancois](https://community.amazonquick.com/u/brancofrancois)\
**Post date:** [December 23, 2024, 7:28am UTC](https://community.amazonquick.com/t/applying-filter-per-metric/40265/4 "2024-12-23T07:28:37Z")

</div>

Dear Andriy,

What you say is correct. The content of the brackets indicate the aggregation level. In Quick Sight 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`