Forum Discussion
Summarizing multiple columns with counts
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.
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.
Hi Anonymous. 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.
4 Replies
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.
Hi Anonymous. 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.
Thank you Ashish_Mathur and littlemojopuppy - Unpivoting other columns worked perfectly.
You are welcome.