Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
admbinp
Frequent Visitor

Matrix to Manage Human Resources between Projects

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       
Employee1234567Total project per employee
Jane1  1  13
Nancy  1 1 13
John 1 11115
Chris1 1  114
Adam11 111 5
Total employee per project322333420

 

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

     
Employee12345Total project per employee
Jane1  1 1
Nancy  1 11
John 1 111
Chris1 1  1
Adam11 111
Total employee per project3223313

 

Error 2:

Even when I switch the Column and Row, still same problem. 

 Employee     
ProjectJaneNancyJohnChrisAdamTotal employee per project
11  1 1
2  1 11
3 1 111
41 1  1
511 111
Total project per employee3223313

 

Has anyone had this situation before? Please help, thank you in advance.

2 REPLIES 2
v-yalanwu-msft
Community Support
Community Support

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.

HarishKM
Impactful Individual
Impactful Individual

@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 .

HarishKM_0-1618816453624.png

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.

HarishKM_2-1618816834896.png

 

 

 

 

unique record = COUNTX(financials,DISTINCTCOUNT(financials[Product]))

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.