Pie chart to count number of resource groups involvement in 145 projects

[MitieNHSFred]

I have, at present, an excel file with 145 project entries (in rows) and, against each project, 9 resource groups in individual columns, showing True or False relating to their involvement.  The data will, eventually be embedded in MSProject and will be output via PowerBI

I'm trying to create a pie that shows the overall count or %age for each of the resource groups, but I can't get my head around which field belongs where.

Any help much appreciated

Cheers

Fred

[Anonymous]

Thanks for the reply from parry2k.

Hi @MitieNHSFred ,

I created simple test data.

As parry2k said, we can do the unpivot operation.

Click on the button to see all the information:

Or create a measure and use it as a tooltip, this will allow you to see the projects to which them belong.

GroupedProjectsMeasure = 
CALCULATE(
    CONCATENATEX(
        FILTER('Table (2)','Table (2)'[Value] = TRUE()),
       'Table (2)'[Project Number],
        "&"
    ),
    ALLEXCEPT('Table (2)','Table (2)'[Attribute])
)

Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[parry2k · Accepted solution]

@MitieNHSFred seems like your data is in pivoted shape and to make it work you should unpivot data, basically resource groups will be on the rows instead of the columns (if I understood it correctly). You can easily unpivot the data in the PQ, by following these steps:

Transform Data -> Select all columns else resource group columns -> right click -> unpivot other columns

It will add two columns, attribute and value, and rename these columns as you see fit, close, and apply.

Now in the Pie visual, you will put attributes on the legend and values on the values section of the pie chart.

[MitieNHSFred]

Hi @parry2k 

Apologies for the delayed confirmation, I had a bit of a "set-to" with the edit interactions as, when I clicked on a pie segment the associated reports didn't update . . . . fixed now.

Thanks for the Un-pivot pointer, that worked a treat

Regards

Fred