Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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])
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])
@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.
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])
🤙