title: DAX Formula for Number of Matches Won/Drawn/Lost
body: <p>I am trying to create several DAX measures in Power Pivot for determining how many matches have been won, drawn and lost by each team in my football table.</p>
  
  <p>I currently have custom DAX formulas for these columns:</p>
  
  <p>Overall Played: <code>=CALCULATE(COUNT(match_result[club_id]), match_main[status_id]=2)</code></p>
  
  <p>Home Goals: <code>=CALCULATE(SUM(match_result[goal]), match_result[home_team]=1)</code></p>
  
  <p>Away Goals: <code>=CALCULATE(SUM(match_result[goal]), match_result[home_team]&lt;&gt;1)</code></p>
  
  <p>Overall Won: ?</p>
  
  <p>Overall Drawn: ?</p>
  
  <p>Overall Lost: ?</p>
  
  <p>This is what my pivot table looks like so far:</p>
  
  <p><a href="https://i.stack.imgur.com/L00Gn.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/L00Gn.png" alt="Pivot Table"></a></p>
  
  <p>This is my relational Data Model structure:</p>
  
  <p><a href="https://i.stack.imgur.com/JsoRF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JsoRF.png" alt="Data Model"></a></p>
  
  <p>Each <code>match_id</code> has two rows in <code>match_result</code> as the home and away side are stored as separate records. The <code>home_team</code> field is a boolean value to determine if the <code>club_id</code> was the home side for the <code>match_id</code> in that row. The <code>goal</code> field records the number of goals that the <code>club_id</code> scored in that <code>match_id</code>.</p>
  
  <p>Here is my workbook: <a href="https://www.excelforum.com/attachments/excel-formulas-and-functions/658773d1579562963-adding-new-columns-in-power-pivot-using-dax-formulas-attachment.zip" rel="nofollow noreferrer">https://www.excelforum.com/attachments/excel-formulas-and-functions/658773d1579562963-adding-new-columns-in-power-pivot-using-dax-formulas-attachment.zip</a>.</p>