Summarizing multiple columns with counts

[Jess80]

I have a database where there is a list of projects in column A, then the following columns are the team members on the different projects.  They are wanting a summary of showing how many projects each team member has been on as one of the dashboard items.  How would I go about getting this set up on Power BI?  Below is a very similfied version of the data provided to give an idea.  Thank you.

[Ashish_Mathur · Accepted solution]

Hi,

In the Query Editor, select the first column and then select "Unpivot Other columns".  Now you should be able to build your desired visual.

[Jess80]

Thank you @Ashish_Mathur and @littlemojopuppy - Unpivoting other columns worked perfectly.  

[Ashish_Mathur]

You are welcome.

[littlemojopuppy · Accepted solution]

Hi @Jess80.  Your data model doesn't do a great job of supporting this...makes it harder than it needs to be.

In Power Query, unpivot the column for team member so you have a list of {Project A, Member 1}, {Project A, Member 2}, {Project B, Member 2}, {Project B, Member 3}, etc.

From there you could create a matrix visual with Project and then Team Member to give you what you're looking for.

[Ashish_Mathur · Accepted solution]

Hi,

In the Query Editor, select the first column and then select "Unpivot Other columns".  Now you should be able to build your desired visual.