title: What does MDX Aggregate() do with a single argument?
body: <p>I understand how to use the MDX Aggregate() and Sum() functions, and the differences between them.</p>
  
  <p>(One interesting one is that the Sum of a measure defined at a higher level in a hierarchy over that level's Children multiplies the measure by the number of children - whereas Aggregate "correctly" returns just the value defined at the higher level).</p>
  
  <p>The documented syntax on <a href="https://msdn.microsoft.com/en-us/library/ms145524.aspx" rel="nofollow">MSDN</a> is:</p>
  
  <pre><code>Aggregate(Set_Expression [ ,Numeric_Expression ])
  </code></pre>
  
  <p>I've always used it with both arguments.  But what does Aggregate do when only the set_expression argument is provided?  The documentation (again from MSDN) is pretty obscure:</p>
  
  <blockquote>
    <p>If a numeric expression is not provided, this function aggregates each measure within the current query context by using the default aggregation operator that is specified for each measure.</p>
  </blockquote>
  
  <p>I tried it in an MDX query like this:</p>
  
  <pre><code>WITH MEMBER WeekSummedTotal AS
  Aggregate([Days].[WeeksAndDays].CurrentMember.Children)
  SELECT 
  {Measures.ThingoCount,Measures.WeekTotal,Measures.WeekSummedTotal} ON 0,
  [Days].[WeeksAndDays].[WeekName] ON 1
  FROM DateGRoupingTest
  </code></pre>
  
  <p>What would this do?  Would Aggregate aggregate the cube's default measure over the set?  Or the set Measures.Members?  Or the set of other measures specified on the 0 axis?</p>
  
  <p>None of these!  The query runs and returns results, but the calcd measure WeekSumTotal shows #Error, with a completely nonsensical error:</p>
  
  <blockquote>
    <p>Aggregate functions cannot be used on calculated members in the measures dimension</p>
  </blockquote>
  
  <p>Now this is true, but completely irrelevant.  None of the other measures in the query is calculated, and in fact the cube doesn't have any calculated members.  So what is Aggregate() actually trying to do here?  Is this error message (again, in MDX!) completely misleading?</p>
  
  <p><strong>ADDITION:</strong> @whytheq in the answer below suggested creating the calculated measure using Aggregate, but creating it on a spare dimension hierarchy rather than in the Measures dimension.  This works, but only if the cross-join with the [All] member of the selected "any old..." dimension is included.
  Creating the measure there also makes it impossible to put the two (base) measures and the calculated measure on the same axis.  If I try to do this:</p>
  
  <pre><code>{Measures.ThingoCount,Measures.WeekTotal,[Ages].[Age Key].WeekSummedTotal} ON 0,
  </code></pre>
  
  <p>I get the deeply-unhelpful error message:</p>
  
  <pre><code>Members, tuples or sets must use the same hierarchies in the  function.
  </code></pre>
  
  <p>which, I think, translates to "I can't make a set using the , (UNION) function between members of Measures and members of [Ages].[Age Key] because they're members of different dimensions".</p>
  
  <p>My conclusion, thanks to your informative answers, is that Aggregate() with a single argument is a tricky beast; I wonder why it was designed with the second argument optional?</p>
  
  <p>I've also noted that trying to create my calculated member on my Ages dimension (only one hierarchy, only one attribute) gives me the misleading error message:</p>
  
  <pre><code>The 'Ages' dimension contains more than one hierarchy, therefore
  the hierarchy must be explicitly specified.
  </code></pre>
  
  <p>unless I explicitly specify the hierarchy.  MDX has so much potential, but the learning curve would be that much gentler if MS had put more effort into making it feed back errors properly.</p>