title: Combining COUNTA() and AVERAGEX() in DAX / Power BI
body: <p>I have a simple data set with training sessions for some athletes. Let's say I want to visualize how many training sessions are done as <strong>an average of the number of athletes</strong>, either in total or divided by the clubs that exist. I hope the data set is somewhat self-describing.</p>
  <p><a href="https://i.stack.imgur.com/AiUPH.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/AiUPH.png" alt="Input data" /></a></p>
  <p>To norm the number of activities by the number of athletes I use two measures:</p>
  <pre><code>TotalSessions = COUNTA(Tab_Sessions[Session key])
  
  AvgAthlete = AVERAGEX(VALUES(Tab_Sessions[Athlete]),[TotalSessions])
  </code></pre>
  <p>I give AvgAthlete as the desired value in both visuals shown below. If I make a filter on the clubs the values are as expected, but with no filter applied I get some strange values</p>
  <p><a href="https://i.stack.imgur.com/INorB.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/INorB.png" alt="Visuals in Power BI" /></a></p>
  <p><strong>What I guess happens is that since Athlete B doesn't do any strength, Athlete B is not included in the norming factor for strength. Is there a DAX function that can solve this?</strong>
  If I didn't have the training sessions as a hierarchy (Type-Intensity), it would be pretty straightforward to do some kind of workaround with a calculated column, but it won't work with hierarchical categories. The expected results calculated in excel are shown below:
  <a href="https://i.stack.imgur.com/yDRUp.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/yDRUp.png" alt="Ground truth excel" /></a></p>
  <p><strong>Data set as csv:</strong></p>
  <pre><code>Session key;Club;Athlete;Type;Intensity
  001;Fast runners;A;Cardio;High
  002;Fast runners;A;Strength;Low
  003;Fast runners;B;Cardio;Low
  004;Fast runners;B;Cardio;High
  005;Fast runners;B;Cardio;High
  006;Brutal boxers;C;Cardio;High
  007;Brutal boxers;C;Strength;High
  </code></pre>