title: Grouping by Quarters with Mondrian / MDX: Behavior of Sum and Count vs Avg
body: <p>I'm trying to aggregate (Sum and Average) over groups of quarters, but I'm getting different results depending on which method I use. I'd like to understand why.</p>
  
  <p>The following basic query illustrates my data (Pentaho SteelWheels sample):</p>
  
  <pre><code>SELECT  {Measures.Sales, Measures.Quantity} ON COLUMNS
  ,       ORDER(
            Time.Quarters.Members,
            Time.CurrentMember.Caption,
            BASC
          ) ON ROWS
  FROM    SteelWheelsSales
  </code></pre>
  
  <p>The result:</p>
  
  <pre><code>Time |  Sales     |Quantity
  -----+------------+---------
  QTR1 |  445094.69 |  4561
  QTR1 |  877418.97 |  8694
  QTR1 | 1111260.1  | 10995
  QTR2 |  564842.02 |  5695
  QTR2 |  847713.97 |  8443
  QTR2 |  869565.24 |  8480
  QTR3 |  687268.87 |  6629
  QTR3 | 1145308.08 | 11311
  QTR4 | 1980178.42 | 19554
  QTR4 | 2117298.82 | 20969
  </code></pre>
  
  <p>So the row headers are captions for the quarters and the different occurrences of QTR1, QTR2, QTR3 and QTR4 each belong to a particular year (So the 1st QTR1 is actually [2003].[QTR1], the 2nd is [2004].[QTR1] and the 3rd is [2005].[QTR1], and so on)</p>
  
  <p>What I'd like to have is a result that treats all occurences of [QTR1] as a single group, and where the cell values aggregate the Sales Quantity measure. Let's say I want to see at least the sum (total quantity for all quarters 1, 2, 3 and 4 regardless of years) and the average (average quantity over all quarters 1, 2, 3, and 4 regardless of years)</p>
  
  <p>Now, I saw this question:</p>
  
  <p><a href="https://stackoverflow.com/questions/17682164/how-can-i-merge-two-members-into-one-in-a-query">How can I merge two members into one in a query?</a></p>
  
  <p>And while that solution does help it requires that I enumerate the quarters that I need to group together. I would like to do that dynamically. </p>
  
  <p>I came up with this query:</p>
  
  <pre><code>WITH
  SET     OrderedQuarters
  AS      Order(
            Time.Quarters.Members,
            Time.CurrentMember.Caption,
            BASC
          )
  SET     UniqueQuarters
  AS      Filter(
            OrderedQuarters,
            OrderedQuarters.Item(
              OrderedQuarters.CurrentOrdinal-1
            ).Caption &lt;&gt; Time.CurrentMember.Caption
          )
  MEMBER  Measures.QuantitySum
  AS      Sum(
            Filter(
              OrderedQuarters,
              UniqueQuarters.Item(
                UniqueQuarters.CurrentOrdinal
              ).Caption = Time.CurrentMember.Caption
            )
          , Measures.Quantity
          )
  MEMBER  Measures.Counter
  AS      Count(
            Filter(
              OrderedQuarters,
              UniqueQuarters.Item(
                UniqueQuarters.CurrentOrdinal
              ).Caption = Time.CurrentMember.Caption
            )
          )
  MEMBER  Measures.[Sum over Count]
  AS      Measures.QuantitySum / Measures.Counter
  MEMBER  Measures.AvgQuantity
  AS      Avg(
            Filter(
              OrderedQuarters,
              UniqueQuarters.Item(
                UniqueQuarters.CurrentOrdinal
              ).Caption = Time.CurrentMember.Caption
            )
          , Measures.Quantity
          )
  SELECT {Measures.QuantitySum
         ,Measures.Counter
         ,Measures.[Sum over Count]
         ,Measures.AvgQuantity} ON COLUMNS
  ,       UniqueQuarters        ON ROWS
  FROM    SteelWheelsSales
  </code></pre>
  
  <p>Which gives this result:</p>
  
  <pre><code>Time | QuantitySum | Counter |  Sum over Count  | AvgQuantity
  -----+-------------+---------+------------------+----------------
  QTR1 |       24250 |       3 |  8083 3333333333 | 8083.3333333333
  QTR2 |       22618 |       3 |  7539.3333333333 | 8083.3333333333
  QTR3 |       17940 |       2 |  8970            | 8083.3333333333
  QTR4 |       40523 |       2 | 20261.5          | 8083.3333333333
  </code></pre>
  
  <p>Now, while the [QuantitySum] and [Sum over Count] measures give me the result I want, I'm not entirely satisfied:</p>
  
  <ol>
  <li>The [QuantitySum] is explicitly calculated with Sum. I would like have it implicitly calculated, since it is already defined in the cube as a sum aggregated measure</li>
  <li>I'm explictly calculating the average in the [Sum over Count] calculated measure. I would like to use Avg() and I tried with Measures.AvgQuantity, but that doesn't seem to behave in the way I expect. It looks like it caculates the average for Q1, and then it repeats that value for the other quarters as well. Why? Sum and Count seem to work exactly as intended, why is Avg so different?  </li>
  </ol>