Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi experts,
I have to create a matrix reporting how many people in a project and how many projects a person is currently involving in.
Ideally, I expect to have a table as below:
Project | ||||||||
Employee | 1 | 2 | 3 | 4 | 5 | 6 | 7 | Total project per employee |
Jane | 1 | 1 | 1 | 3 | ||||
Nancy | 1 | 1 | 1 | 3 | ||||
John | 1 | 1 | 1 | 1 | 1 | 5 | ||
Chris | 1 | 1 | 1 | 1 | 4 | |||
Adam | 1 | 1 | 1 | 1 | 1 | 5 | ||
Total employee per project | 3 | 2 | 2 | 3 | 3 | 3 | 4 | 20 |
As I'm creating a matrix in Power BI, I can't create the total column as the table I described above. I can only have the total row at the bottom of the matrix or the total column have sum of the value, the other's value is always 1.
Situation 1:
I used Employee Name as Column, Project as Row, and Value is Count Distinct EmployeeID -> Total project per emoployee is 1
If I switch the Value to Count Distrinct ProjectID -> total employee per project is 1
Error 1:
Project | ||||||
Employee | 1 | 2 | 3 | 4 | 5 | Total project per employee |
Jane | 1 | 1 | 1 | |||
Nancy | 1 | 1 | 1 | |||
John | 1 | 1 | 1 | 1 | ||
Chris | 1 | 1 | 1 | |||
Adam | 1 | 1 | 1 | 1 | 1 | |
Total employee per project | 3 | 2 | 2 | 3 | 3 | 13 |
Error 2:
Even when I switch the Column and Row, still same problem.
Employee | ||||||
Project | Jane | Nancy | John | Chris | Adam | Total employee per project |
1 | 1 | 1 | 1 | |||
2 | 1 | 1 | 1 | |||
3 | 1 | 1 | 1 | 1 | ||
4 | 1 | 1 | 1 | |||
5 | 1 | 1 | 1 | 1 | 1 | |
Total project per employee | 3 | 2 | 2 | 3 | 3 | 13 |
Has anyone had this situation before? Please help, thank you in advance.
Hi @admbinp ,
As @HarishKM said,you can try to use countx function.
Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your table and your problem.
Best Regards,
Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@admbinp Hey
drag that project id (if data type is text ) and employee in matrix row coloumn then drag your unique employee id = countrows(employee_id) once you will do you will find like this .
when you select distinctcount then for row it will show only 1 at total but for col you will get correct result but if you will use countrow then you will have the sum at both level .
Pasting image for your ref.
Left with disticnt count and right with countrows.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
90 | |
84 | |
71 | |
49 |
User | Count |
---|---|
141 | |
121 | |
112 | |
59 | |
58 |