title: DAX ALLEXCEPT to sum by category of multiple dimension tables
body: <p>I would like to calculate total by category. The category is in the dimension table. </p>
  
  <p>Here is sample file: 
  <a href="https://drive.google.com/uc?authuser=0&amp;id=1TRUUgTFpFc6EnVYuhl8xC6IRq7VPuz7K&amp;export=download" rel="nofollow noreferrer">DAX ALLEXCEPT total by category.pbix</a></p>
  
  <p>I have the following model:</p>
  
  <p><a href="https://i.stack.imgur.com/EPb6K.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/EPb6K.png" alt="enter image description here"></a></p>
  
  <p>These are my expected results. Total by Color:</p>
  
  <p><a href="https://i.stack.imgur.com/Nkc84.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Nkc84.png" alt="enter image description here"></a></p>
  
  <p>I thought I could achieve expected results by the following measure:</p>
  
  <pre><code>ALLEXCEPT_color =
  CALCULATE (
      [Sales],
      ALLEXCEPT (
          FactTable, -- surprisingly 'dim1' table in that place gives wrong results
          dim1[Color]
      )
  )
  </code></pre>
  
  <p>Or alternatively using method suggested by Alberto Ferrari <a href="https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/" rel="nofollow noreferrer">https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/</a>:</p>
  
  <pre><code>ALL_VALUES_color = 
      CALCULATE (
          [Sales],
          ALL (FactTable), -- again, 'dim1' produces wrong results, has to be FactTable
          VALUES ( dim1[Color] )
      )
  </code></pre>
  
  <p>Both these measures work and return proper results. However they multiply displayed results making Cartesian product of all the dimensions. Why? How to prevent it?</p>
  
  <p>I achieve expected results with measure:</p>
  
  <pre><code>Expected_Results_Color =
  IF (
      ISBLANK ( [Sales] ),
      BLANK (),
      [ALLEXCEPT_color]
  )
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/cLsaj.gif" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cLsaj.gif" alt="enter image description here"></a></p>
  
  <p>Probably I am missing something about <code>ALLEXCEPT</code> function so I do not get what I want for the first shot. What is the logic behind using ALLEXCEPT function with multiple tables, especially with far off dimensions, away from the center of star schema.</p>
  
  <p>What pattern to use? <a href="https://community.powerbi.com/t5/Desktop/Allexcept-from-two-tables/td-p/255925" rel="nofollow noreferrer">Here</a> I found promising solution which looks like this:</p>
  
  <pre><code>ByCategories =
  CALCULATE (
      SUM ( FactTable[Sales] ),
      ALLEXCEPT (
          dim1,
          dim1[Color]
      ),
      ALLEXCEPT (
          dim2,
          dim2[Size]
      ),
      ALLEXCEPT (
          dim3,
          dim3[Scent]
      )
  )
  </code></pre>
  
  <p>But as I tested it before it does not work. It does not aggregate [Sales] by dimensions but produces [Sales] as they are. </p>
  
  <p>So I found out that this is the correct direction:</p>
  
  <pre><code>ByCategories =
  CALCULATE (
      SUM ( FactTable[Sales] ),
      ALLEXCEPT (
          FactTable, -- here be difference
          dim1[Color],
          dim2[Size],
          dim3[Scent]
      )
  )
  </code></pre>
  
  <p>I speculate there might be also another way.</p>
  
  <pre><code>Measure = 
      var MyTableVariable =
      ADDCOLUMNS (
          VALUES ( dim1[color] ),
          "GroupedSales", [Sales]
      )
      RETURN
          ...
  </code></pre>
  
  <p>If only we could retrieve single scalar value of GroupedSales from MyTableVariable and match it with appropriate color in table visual. </p>
  
  <p>I would be very grateful for any further insights in calculating total for category.</p>