Forum Discussion
How to count occurrences for a Power BI Report Table visual
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 |
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.
You can do by using advance grouping.
- Advance Grouping
- Output:
4 Replies
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.
You can do by using advance grouping.
- Advance Grouping
- Output:
Thanks, that worked!!!
Thanks, that worked!!!
