title: Using Count and Group By in Power BI
body: <p>I have a table that contains data about different benefit plans and users enrolled in one or more of those plans. So basically the table contains two columns representing the benefit plan counts and total users enrolled in those plans.  </p>
  
  <p>I need to create visualization in Power BI to represent the number of total users enrolled in 1 plan, 2 plans, 3 plans, ...etc. </p>
  
  <p>I wrote the query in sql to get the desired result but not sure how do I do the same in power BI. </p>
  
  <p>Below is my sql query:</p>
  
  <pre><code>SELECT S.PlanCount, COUNT(S.UserName) AS Participants 
  FROM (
      SELECT A.Username, COUNT(*) AS PlanCount 
      FROM [dbo].[vw_BenefitsCount_Plan_Participants] AS A
      GROUP BY A.username
  )AS S
  GROUP BY S.PlanCount
  ORDER BY S.PlanCount
  </code></pre>
  
  <p>The query result is below image:</p>
  
  <p><a href="https://i.stack.imgur.com/PBKSd.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/PBKSd.png" alt="enter image description here"></a></p>
  
  <p>So here, PlanCount column represents the total different benefit plans that users are enrolled in. For e.g. the first row means that total of 6008 members are enrolled in only 1 plan, whereas row 2 displays that there are total of 3030 members who are enrolled in total of 2 plans and similarly row 5 means there are only 10 users who are enrolled in total of 6 plans. </p>
  
  <p>I am new to Power BI and trying to understand DAX functions but couldn't find a reasonable example that could help me create my visualization.</p>
  
  <p>I found a something similar <a href="https://stackoverflow.com/questions/38570755/min-value-with-group-by-in-power-bi-desktop">here</a> and <a href="https://stackoverflow.com/questions/49535920/count-and-group-by-power-bi">here</a> but they seem to be more towards single count and group by usage. </p>
  
  <p>Here is a simple example. I have a table of home owners who have homes in multiple cities. </p>
  
  <p><a href="https://i.stack.imgur.com/FpkwC.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FpkwC.png" alt="enter image description here"></a></p>
  
  <p>Now in this table, Alex, Dave and Julie have home in 1 city (basically we can say that these 3 people own just 1 home each). Similarly Jim owns a total of 2 homes and Bob and Pam each have 3 homes in total. </p>
  
  <p>Now the output that I need is a table with total number of home owners that own 1 home, 2 homes and so on. So the resulting table in SQL is this.</p>
  
  <p><a href="https://i.stack.imgur.com/ASOQa.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ASOQa.png" alt="enter image description here"></a></p>
  
  <p>Where NameCount is basically count of total home owners and Homes is the count of total homes these home owners have. </p>
  
  <p>Please let me know if this helps. </p>
  
  <p>Thanks. </p>