5 Replies Latest reply: Aug 17, 2018 2:44 AM by Rasmus Nielsen RSS

    Calculate avg of avg with aggr

    Rasmus Nielsen

      Hey guys,

       

      My data model looks like:

      datebrandmypricecompprice
      2018-01-01B11,0001,000
      2018-01-01B21,0001,111
      2018-01-01B31,0001,222
      2018-01-01B41,0001,333
      ............

       

      Screen Shot 2018-08-15 at 15.41.04.png

      Lets says that B1 is my own brand. Each row contains my brand and the competing brand price. (1st row the competing price is my own price). I am trying to have a bar chart with a bar for each brand and their distance to my own price.

       

      I am trying to populate the following table:

      Screen Shot 2018-08-15 at 15.33.11.png

      The B2, B3, B4 column is the average of the distances to my own price:

      Avg(myprice) - Avg({$<[brand]={'B2'}>}compprice)
      
      

       

      I would like a column with the average of the averages. The correct number is shown in the column "Desired" and is just the result of averaging the values of the three columns B2, B3, B4.

      But I am trying to use this in a chart as well and I can not get my expression to yield the same result. I am using this expression in the "Test Avg of Avg" column:

      Avg(Aggr(Avg(myprice) - Avg(compprice), date, brand))
      
      

       

      Appreciate any feedback.

      Thanks