Showing Product sales over years + corresponding Product group in separate charts

[100Glizzy]

Hi all,

I've been browsing the community for a while and haven't found a solution for my problem. I'm trying to build an app which shows an overview of various products we have and the total sales of said product in one chart, along with another chart showing the corresponding product group sales. I feel like it's something quite easy to do but I just can't figure it out..

I've created a filter pane which shows all the products we offer from which I want the user to make a single selection. Based on this selection, I have chart showing the sales of this product over the past 5 years. 

Ideally in another chart, I want to show the total sales over the past 5 years of the product group.

I haven't figured out how to make the connection from a selected product (in the filter pane) to the corresponding product group in another chart. From the chart below, I hope you can see what I mean. Ideally, the top chart should total sales of all products.

Table:

LOAD * INLINE [

Product Group, Product, Year, Sales,

Dairy, Cheese, 2010, 225
Dairy, Cheese, 2011, 230
Dairy, Cheese, 2012, 235
Dairy, Cheese, 2013, 245
Dairy, Cheese, 2014, 255
Dairy, Cheese, 2015, 265

Dairy, Milk, 2010, 225
Dairy, Milk, 2011, 230
Dairy, Milk, 2012, 235
Dairy, Milk, 2013, 245
Dairy, Milk, 2014, 255
Dairy, Milk, 2015, 265

Dairy, Yogurt, 2010, 225
Dairy, Yogurt, 2011, 230
Dairy, Yogurt, 2012, 235
Dairy, Yogurt, 2013, 245
Dairy, Yogurt, 2014, 255
Dairy, Yogurt, 2015, 265
];

Any help or a point in the right direction would be greatly appreciated!

[vincent_ardiet_ · Accepted solution]

An expression like this should do the job (removing the selection on product and taking everything in the possible product group, meaning all products in the same product group as the one currently selected):
Sum({<[Product],[Product Group]=p([Product Group])>}Sales)

[marcus_sommer]

The expression for product groups might be adjusted like:

sum({< [Product Group] = p([Product Group])>} Sales)

[100Glizzy]

Thank you!! Works like a charm 🙂

What does the p indicate by p([Product Group])?

[vincent_ardiet_]

P() means all possible values.
Creating a set analysis is like manipulating filters in the UI. If you select a Product, the list of product groups will be reduced to matching values. Then, if you select "all possible" in this list of values and unselect the product, this is what you want to achieve.