Using Count and Group By in Power BI

user_773527 0
Viewed 2676

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:

enter image description here

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.

enter image description here

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.

enter image description here

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.

powerbi count group-by sql-order-by sql
2 Answers

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.

user_93328140
user_113758680
Related