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

[Kasey-Gagne]

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 solution]

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

[Kasey-Gagne]

Thank you so much for the reply!!

Using Fusion to parse and record these values is a brilliant idea! We would want a total count across all portfolios/programs so I will have to do some playing around but this is getting me off to a great start.

[Daniel_Clarke]

Happy to assist! Reach back if you run into an snags in the logic and we'll see what else we can do to help!