title: In OLAP cube wrong Grand Total when attribute is filtered
body: <p>A user trying to check the Sales Amount per Salesperson. Sample data:</p>
  
  <pre><code>Salesperson   Sales Amount    
  001                   1000    
  002                    500    
  003                    750
  Grand Total:          2250
  </code></pre>
  
  <p>It looks fine, but we have the following hierarchy <code>Company &gt; Class &gt; Group &gt; Subgroup</code> in the cube and if a user tries to use this hierarchy in filters - Grand Total fails (if any attribute is unchecked in this hierarchy). Sample:</p>
  
  <pre><code>Salesperson   Sales Amount    
  001                   1000    
  002                    500    
  003                    750    
  Grand Total:           350
  </code></pre>
  
  <p>I've noticed the same problem before when we tried to filter Date attribute, if not every day of the month was selected it shown wrong Grand Total too.</p>
  
  <p>Have you an idea why it happens and how to fix it?</p>
  
  <p>Sales Amount is physical measure (not calculated measure), it is selected from SQL view (the same happens with every fact). </p>
  
  <p><a href="https://i.stack.imgur.com/X2db9.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/X2db9.png" alt="Wrong Grand Total"></a></p>
  
  <p>I've asked the same question <a href="https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2761cc7c-eccb-4bca-8836-e655a22a53ab/wrong-grand-total-when-attribute-is-filtered?forum=sqlanalysisservices" rel="nofollow noreferrer">here</a>, but nobody could answer it.</p>
  
  <p>I've tried to delete all MDX calculations (scopes), but still Grand Total was incorrect. </p>
  
  <p><a href="https://i.stack.imgur.com/OkI3e.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/OkI3e.png" alt="Clean MDX Calculations"></a></p>
  
  <p><a href="https://i.stack.imgur.com/qOYFd.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qOYFd.png" alt="Wrong Grand Total"></a></p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I've noticed that the problem occurs when filtering like that:</p>
  
  <p><a href="https://i.stack.imgur.com/iCCM0.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/iCCM0.png" alt="Hierarchy filter"></a></p>
  
  <p>1 element selected from the first level of the hierarchy, 1 element from 2nd level and 1 element from the 3rd level of hierarchy as in the image above.</p>
  
  <p>If the 3rd level isn't filtered it shows good Grand Total.</p>
  
  <p><strong>EDIT 2</strong></p>
  
  <p>I've tried to trace on SSAS, it returns exactly the same output as in Excel. It generated the following MDX when using Salesperson dimension on the rows:</p>
  
  <pre><code>SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS, 
  NON EMPTY { ([Salesperson].[Salesperson].[Salesperson].ALLMEMBERS ) } 
  DIMENSION PROPERTIES MEMBER_CAPTION, 
  MEMBER_UNIQUE_NAME ON ROWS FROM ( 
  SELECT ( {  [Item].[Class - Group - Subgroup].[Class].&amp;[XXX]&amp;[1.], 
              [Item].[Class - Group - Subgroup].[Group].&amp;[XXX]&amp;[2.]&amp;[2.2.], 
              [Item].[Class - Group - Subgroup].[Subgroup].&amp;[XXX]&amp;[2.]&amp;[2.3.]&amp;[2.3.1.] } 
  ) ON COLUMNS FROM ( SELECT ( { [Company].[Company].&amp;[XXX] } ) ON COLUMNS 
  FROM [Sales])) 
  WHERE ( [Company].[Company].&amp;[XXX], [Item].[Class - Group - Subgroup].CurrentMember ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
  </code></pre>
  
  <p>This MDX generated without Salesperson dimension:</p>
  
  <pre><code>SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS 
  FROM ( SELECT ( { [Item].[Class - Group - Subgroup].[Class].&amp;[XXX]&amp;[1.], 
  [Item].[Class - Group - Subgroup].[Group].&amp;[XXX]&amp;[2.]&amp;[2.2.], 
  [Item].[Class - Group - Subgroup].[Subgroup].&amp;[XXX]&amp;[2.]&amp;[2.3.]&amp;[2.3.1.] } ) ON COLUMNS 
  FROM ( SELECT ( { [Company].[Company].&amp;[XXX] } ) ON COLUMNS 
  FROM [Sales])) WHERE ( [Company].[Company].&amp;[XXX], [Item].[Class - Group - Subgroup].CurrentMember ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
  </code></pre>
  
  <p>I've noticed even if I'm not using any dimension on the rows (in samples above I've used Salesperson dimension) it shows wrong Grand Total.</p>
  
  <p>For example it shows:</p>
  
  <pre><code>Sales Amount 
  350
  </code></pre>
  
  <p>And when using Salesperson dimension on the rows:</p>
  
  <pre><code>Salesperson   Sales Amount    
  001                   1000    
  002                    500    
  003                    750    
  Grand Total:           350
  </code></pre>