title: Unexpected Excel Pivot behaviour with SQL Server Tabular model
body: <p>We use Excel from Office 365 with a SQL Server 2019 Tabular model</p>
  <p>I have a &quot;strange&quot; behaviour of Excel Pivot regarding the level at which calculations are requested from DAX.</p>
  <p><a href="https://i.stack.imgur.com/lLmBz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/lLmBz.png" alt="Brand Level result" /></a></p>
  <p>We have a measure that behaves differently depending on the granularity level you query it at.</p>
  <p>In the example above, the measure is queried at &quot;BRAND_NAME&quot; level with some filters.</p>
  <p>The sum of the Brand numbers does not equal the total, which is &quot;fine&quot; as it is a known side effect of that measure in this particular query.</p>
  <p>Now, if we add a layer underneath, then the Excel Pivot shows a behaviour that is a problem for us</p>
  <p><a href="https://i.stack.imgur.com/JlF44.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JlF44.png" alt="Item Level result" /></a></p>
  <p>I'll do my best but it is not easy to explain...</p>
  <p>What the Excel pivot is showing us is</p>
  <ul>
  <li>the TOTAL_SALES_UNIT for the Item SAMSUNG &quot;SP 54J8 HFR&quot; (evaluated at <strong>ITEM</strong> Level)</li>
  <li>the TOTAL_SALES_UNIT for the Item SAMSUNG &quot;TRADE MODEL&quot; (evaluated at <strong>ITEM</strong> Level)</li>
  <li>the TOTAL_SALES_UNIT for the Brand &quot;OTHERS&quot; (evaluated at <strong>BRAND</strong> Level, probably because there is no record underneath the &quot;OTHERS&quot; Brand...)</li>
  </ul>
  <p>Our measure TOTAL_SALES_UNIT is complicated and coded in such a way that it would return 0 if evaluated at ITEM level under the OTHERS brand...</p>
  <p>BUT there is no ITEM under the OTHERS Brand and it looks like Excel Pivot somehow decides to evaluate our measure at the Brand level instead of the Item level...</p>
  <p>Running a similar request under SSMS returns the result we were expecting</p>
  <p><a href="https://i.stack.imgur.com/EBQOv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/EBQOv.png" alt="Equivalent Query in SSMS" /></a></p>
  <p>Question: Is there a way in Excel that we could control this pivot table behaviour?</p>