7 Replies Latest reply on Aug 30, 2013 8:55 PM by Joshua Milligan

    Aggregate Awareness in Measures

    Matt Stoffa

      I have attached a relatively simple sample worksheet and need some help creating a calculated measure.

       

      It currently includes a measure called AVG(Total_GCs).  This calculates the average of the Total_GCs value from the data.  Because the attached worksheet includes the dimension 'Qtr Hour', this measure works correctly.

       

      What if I remove the 'Qtr Hour' dimension from the Rows shelf?  The sheet now shows 24 rows for the appropriate Hours.  Unfortunately, the AVG(Total_GCs) is still showing the average of the quarter hours instead of the average HOURLY GCs.

       

      Similarly, what if I then add a dimension to the Rows shelf for WEEKDAY(T_Date).  The sheet correctly shows the 168 rows (24 hours * 7 days of the week) but the AVG(Total_GCs) is still showing the average of the quarter hours instead of the average per HOUR per Weekday.

       

      What I need is a measure that is 'aware' of its own level of aggregation.  If the sheet groups by hour, this should be the average HOURLY Total_GCs (sum to the hourly level first, and then average them).  If the sheet groups by Hour and Weekday, this should sum the Total_GCs first and then calculate the average.

       

      Expected Results:

      • Apply a filter to the Hour dimension and select '12;00' only (just the noon hour).
      • Remove the Qtr Hour dimension from the Rows shelf (just drag it off the screen)
      • The January 2012 data should be 146.29 .... It should NOT be 36.6.
      • The July 2012 data should be 166.58 ... It should NOT be 42.0.

       

      Is there any way to do this?