4 Replies Latest reply: Feb 6, 2018 2:40 PM by Mark Gertgen RSS

    Aggregate with new dimension - help with grain

    Mark Gertgen

      Hey all, I have an app that has 30 days of employee activity.  In it I have a table that shows job titles and the average number of activities per day for each job title.


      TitleAverage Activities per Day
      Job 1100
      Job 2


      Job 350
      Job 410


      Avg(Aggr(Sum([Activity Counter]), [Date], [Job Title]))


      What I want to do now is add an employee dimension and show their activity for the current day and still show the average activity for their job title.


      TitleEmployeeActivities TodayAverage Activities per Day
      Job 1John78100
      Job 1Mary112100
      Job 2Mark224200
      Job 3Sally4750
      Job 4Sara1310


      I can add the employee and Sum([Activity Counter]), but changing the grain of the table breaks the Average Activities per Day calculation.  I can't seem to find an example of how to do this. I tried to have it ignore the Employee dimension but it doesn't work.


      Avg(Aggr(Sum([Activity Counter]), [Date], [Job Title] <[Employee]>))


      I know my aggregation has to be at the same grain (or finer) of the chart but I need to keep the average per day for each job title.


      Any help would be appreciated!