title: Power BI - DAX measure to reference a list of values. Calculate net turnover per broker - more elegant solution?
body: <p>Below is a simplified version of transaction data for stocks.</p>
  
  <pre><code>StockData = 
  DATATABLE (
      "STOCK", STRING,
      "Date", DATETIME,
      "Buyer", STRING,
      "Seller", STRING,
      "Turnover", INTEGER,
      {
           { "AAPL", "2019/04/07", "GSI", "BRC", 100 },
           { "AAPL", "2019/04/07", "CITI", "JPM", 500 },
           { "AAPL", "2019/04/07", "JPM", "GSI", 700 },
           { "AAPL", "2019/04/08", "GSI", "JPM", 300 },
           { "AAPL", "2019/04/08", "GSI", "CITI", 800 },
           { "AAPL", "2019/04/08", "JPM", "BRC", 400 },
           { "MSFT", "2019/04/07", "GSI", "GSI", 500 },
           { "MSFT", "2019/04/07", "JPM", "BRC", 700 },
           { "MSFT", "2019/04/07", "BRC", "GSI", 800 },
           { "MSFT", "2019/04/08", "GSI", "BRC", 500 },
           { "MSFT", "2019/04/08", "GSI", "JPM", 600 },
           { "MSFT", "2019/04/08", "CITI", "BRC", 500 }
      }
  )
  </code></pre>
  
  <p>Goal is to calculate net turnover by broker per day.</p>
  
  <p>I can achieve this by following DAX measure</p>
  
  <pre><code>Test BRC Net Turnover = 
  VAR TotalBuy = CALCULATE(SUM(StockData[Turnover]),StockData[Buyer] = "BRC")
  VAR TotalSell = CALCULATE(SUM(StockData[Turnover]),StockData[Seller] = "BRC")
  Return TotalBuy - TotalSell
  </code></pre>
  
  <p>However, to get net turnover for all  (Four) brokers in example data above I have to rewrite the measure four times with different critera, i.e., rather than using "BRC" I have to use "GSI" etc...</p>
  
  <p>Real data set consists of 50 different broker codes so the solution with 50 different measures is not feasible.</p>
  
  <p>How can I make this DAX function iterate over all Broker codes in my data set. Taking into consideration filters for the stock. I.e., if a typical broker code doesnt exist for MSFT but for AAPL the measure will take that into consideration.</p>
  
  <p>I have tried the <code>values</code> function to return a list of all brokers, without any success.<a href="https://i.stack.imgur.com/N8VEo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/N8VEo.png" alt="enter image description here"></a></p>
  
  <p>Above is what I would like to achieve with one single measure.</p>
  
  <p>Many thanks</p>