How to count occurrences for a Power BI Report Table visual

[strongr]

in Microsoft power bi given a source table of resource names and the projects and task they are assigned to how do I count for each resource name number of task  task they have and but that total value in a summary table column and also count the number of task they have and then put that total count in a separate column in the summary table?

SOURCE TABLE

Resource name	Projects	Task
John	Project A	Task 1A
John	Project B	Task 1B
Sally	Project A	Task 2A

SUMMARY TABLE:

Resource name	Number of Projects	Number of Task
John	2	2
Sally	1	1

[Anonymous · Accepted solution]

Hi @strongr 

In power query, you can use group by to achieve this goal.

You can duplicate the table first

Then in the new table you can create a new step and input the following code.

Table.Group(#"Changed Type", {"Resource name"}, {{"Number of Project", each List.Count(List.Distinct([Projects])), Int64.Type}, {"Number of Tasks", each List.Count(List.Distinct([Task])), Int64.Type}})

Output

And you can refer to the attachments.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[strongr]

Thanks, that worked!!!

[strongr]

Thanks, that worked!!!

[KRISHP1234 · Accepted solution]

You can do by using advance grouping.

• Advance Grouping

• Output:
  

[Anonymous · Accepted solution]

Hi @strongr 

In power query, you can use group by to achieve this goal.

You can duplicate the table first

Then in the new table you can create a new step and input the following code.

Table.Group(#"Changed Type", {"Resource name"}, {{"Number of Project", each List.Count(List.Distinct([Projects])), Int64.Type}, {"Number of Tasks", each List.Count(List.Distinct([Task])), Int64.Type}})

Output

And you can refer to the attachments.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.