Qlik Sense - Filter both Groups (created by Alternate States) in one filter

[Gavin_FBu]

Hi,
I have a data set for store sales by products and try to allow user to slect ther own store and comparable the other store they like. I created:

- 2 groups by alternate states (Primary / Comparable) and a filter pane for each groups at the top for user to select store
- 2 KPI boxes from 2 created measures Primary $ = sum({[Primary]}[Sales]) and Comparable$ = sum({[Comparable]}[Sales])
- 1 bar chart by cateogies with both groups of sales $

When I click on one of the cateory name in the chart both KPI boxes didn't filter by category. I have a play around on the Alternate states > State from the bar chart table and found if I set the State to:

<inherited> = both KPI don't change
<Primary> = only Primary KPI sales $ change but Comparable don't
<Comparable> = only Comparable KPI sales $ change but primary don't

I am look for a way to select the cateogry in the chart and both KPI will reflect. I do not want to create any extra filter pane eg: primary category filter pane and compareable category filter pane because I will have more level to compare with (eg: sub category level, product level...etc)
Please help.

[jwjackso]

Try using the P()  function in the KPIs.  Use the default state for the chart.   I'm assuming the category field is [Category].

Primary $ = sum({[Primary]<[Category]=P({$}[Category])>}[Sales]) and Comparable$ = sum({[Comparable] <[Category]=P({$}[Category])>}[Sales])

[Gavin_FBu]

@jwjacksoMant Thanks and its work, however If i have a few more layers require this function, how to modify this formula?  

For example apart from Category, I also have Sub-Category and Product that will going to build a chart like category.

[jwjackso · Accepted solution]

You can add multiple P() functions to the Set Analysis, see example below:

Primary $ = sum({[Primary]<[Category]=P({$}[Category]),[Sub-Category]=P({$}[Sub-Category])>}[Sales])

[Gavin_FBu]

🤙