show avg value by group based on selection?

[Anonymous]

I have an excel sheet as attached, the sheet gets updated data weekly.

Here is what I want to do.

When end user picks a product, it will show the product's group and subgroup avg sales respectively.

if more than one product picked, it will show all picked products' groups and subgroups avg sales too.

For example, in case product C, D, F are picked, I need show avg sales of the group (G1+G2) which is $9,

avg sales of the subGroup (s3+s4) which is $10.

How to write this expression? much appreciate anyone could share the calculation script.
I could not open qvw file as it is personal edition, thanks.

[maxgro · Accepted solution]

try in a textbox the expression

avg({$ <Product=,Goup=P(Goup)>} [Avg Sales per Store])

[Anonymous]

perfect! Much appreciated.

[Anonymous]

Sorry I am back again with further question on this topic.

Please see the attachment, I would like to do

1) when product A is selected, ONLY A shows in straight table. If A and B selected, ONLY A and B shows.

2) same time, the column "Avg Sales per store" shows all selected products' individual avg $ per week, for instance, if A is shown, the column value should be is $9.5 (calculation= (6+13)/2)

3) the column goup avg$ would show $9.58 in case A is selected

4) the column subgroup avg $ would show $10.28 in case A is selected

If nothing selected, the straight table shows all products and their corresponding avg $ values.

I thought I put correct expression that works on text box, but it does not work out as the expectation in straight table. looks only the Total Mode shows correctly (indicated on the attached pic).Why? Can you share the working expression for 2, 3, 4?

I also attached a screen shot with my comments, hopefully it could help you understand what I am looking for.

Again, please share the expression here, I am using personal edition that may NOT open others qvw file. Many thanks.