Creating a chart for a multi-select field using calc fields

Question

I have a multi-select field on all of our custom forms to select the company(s) the project applies to. The end goal is to have ONE pie chart that shows how many projects for each company so that we can allocate resource costs to those companies accordingly.

I've tried creating separate calc fields for each company that populates a 1 or a 0, which functions as it should. However, I cannot seem to figure out how I can now use these values in 1 chart... Is there a better way to handle this or am I out of luck and stuck exporting reports to excel to do the calculations?

Field 1:

IF({DE:Company}="CompanyA", 1, 0)

Field 2:

IF({DE:Company}="CompanyB", 1, 0)

Field 3:

IF({DE:Company}="CompanyC", 1, 0)

Things to note:

I'm only showing 3 here as an example, however we have ~20 companies in the drop down
Creating separate projects per company to avoid the multi-select field is not an option

Daniel_Clarke · Accepted Answer

Hi @kasey-gagne! A few thoughts here...

If you just use one calc field holding the {DE:Company} value it will show you all individual selections, along with any that are multi-select in a comma delimited format... likely that you did this and were not satisfied but just calling it out in case you did not!
In addition to the previous view, you could create 20x reports using a "contains" filter to isolate for each one, this eliminates the issue of the multi-select
Or if you want one flexible report you can use a report prompt that uses "contains" and then search for each company in question

Depending on how often you want to reference your updated pie chart you can use the above information to manually fill in those counts into a custom form on the leading portfolio/program. Build the fields / view in the same order as your companies and type > tab > type > tab to fill in... would only take 60 seconds to fill it and keep the graphics matching in a new report in Workfront.

If you have Fusion this is improvable as well, search all projects for {DE:Company}, parse and record values to the leading portfolio/program as appropriate...

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded