Using Count and Group By in Power BI

Question

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.

I need to create visualization in Power BI to represent the number of total users enrolled in 1 plan, 2 plans, 3 plans, ...etc.

I wrote the query in sql to get the desired result but not sure how do I do the same in power BI.

Below is my sql query:

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

The query result is below image:

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.

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.

I found a something similar here and here but they seem to be more towards single count and group by usage.

Here is a simple example. I have a table of home owners who have homes in multiple cities.

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.

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.

Where NameCount is basically count of total home owners and Homes is the count of total homes these home owners have.

Please let me know if this helps.

Thanks.

Can you post a simplified example input and expected output? I think I understand and can help, but a concrete example makes things much easier. — Alexis Olson
@AlexisOlson I tried to simplify the problem. Please let me know if that helps. — Naphstor
@PuneetMittal, I wasn't able to do the group by in power bi but I was able to update my incoming model in SSAS and then import the data in power bi. For now it worked, but I am still new to this so still researching on the power bi functionalities. — Naphstor

Javier Sierra Javier Sierra · Accepted Answer · 2018-09-06T10:55:28

If I understood fine, you have a table like this:

BenefitPlan | User

1 | Max

1 | Joe

2 | Max

3 | Anna

If it's ok, you can simply use a plot bar (for example) where the Axis is BenefitPlan and Value is User. When you drag some column in Value field, it will be grouped automaticaly (like group by in SQL), and by default the groupping method is count.

Hope it helps.

Regards.

Using Count and Group By in Power BI

2 Answers