7 Replies Latest reply on Feb 17, 2018 8:50 AM by Jim Dehner

    Properly aggregating COUNTD expression

    Glenn Kuly

      Hello all, I'm preparing a worksheet showing a simple metric, average number of phone calls per shift for several employees, compared against the overall calls/per shift mean average. My difficulty is that while i can the total number of shifts per employee on a row-by-row basis (using a COUNTD expression), I can't figure out how to aggregate the expression, in order to calculate and show an overall mean average figure as a standalone calculation (i.e. to show as a summary stat).

       

      This is a simplification of what my data looks like:

       

      book1_2.png

       

      A crosstab of the data shows that the employees worked a total of 7 shifts and answered 30 calls in total:

       

      book1_3.png

       

      In Tableau, I can calculate everything i need in a table (a calls per shift average for each employee, and an overall mean average). My difficulty is with column 2 of the following view: number of shifts worked is calculated properly for each employee, but the total figure is wrong. The calculated field i'm using is Number of Shifts: COUNTD(DATEPART('dayofyear', [Date]))

       

      I get that i can switch Total Using to Sum on the pill to show the correct total in the table, but I'm wondering how to reformulate my calculated field to produce the correct total automatically (which i need to be able to do to calculate a mean average outside this table). I've tried wrapping the calculated field in a TOTAL(), but this did nothing. Can someone show me the way forward here?

       

      book1_1.png

       

      Workbook attached v10.5

       

      -- Glenn