3 Replies Latest reply: Apr 29, 2015 6:51 AM by surfman joe RSS

    show avg value by group based on selection?

      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.

        • Re: show avg value by group based on selection?
          Massimo Grossi

          try in a textbox the expression


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

            • Re: show avg value by group based on selection?

              perfect! Much appreciated.

              • Re: show avg value by group based on selection?

                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.