Calculated Field / Column Question

[gurltech]

Hi.  I have 4 custom fields that have large single-select dropdown fields; all the columns have the same values.  The fields are added as individual columns in a report where the user can select value in each column for each project.  My report columns have this layout > Project, Menu 1, Menu 2, Menu 3, Menu 4

Project Name	Menu 1	Menu 2	Menu 3	Menu 4
Test 1	A1	B2	C1	A2
Test 2	B2	A1	B1	A2
Test 3	A1	C1	B2	B1

I have created a calculated field that will show the number of instances each that value occurs per project & built another report.

Project Name	A1 Value Count	A2 Value Count	B1 Value Count	B2 Value Count	C1 Value Count
Test 1	1	1	0	1	1
Test 2	0	1	1	1	0
Test 3	1	0	1	1	1

My question - Is there a way to create a calculated column to add the totals of each Value Count column?  The goal is to have the total from each column reported on a graph (i.e. a pie chart showing there are 2 results for A1 Value Count, 2 for A2 Value Count, 2 for B1 Value Count, 3 for B2 Value Count, 2 for C1 Value Count.  I have tried different ways in text mode but haven't been able to work it out.  Thanks!

[VictoriaLinn · Accepted solution]

Hi @gurltech - The way to get a total for the columns would be to use an aggregated grouping. To do so, turn on the grouping and then for each column turn on the count option in the "Summarize this column by:" field. It should then do the totals in the grouping on the report. 

Unfortunately, this won't work for charts and only works for lists.

[RandyRoberts]

lol, I was typing out the same answer as you at the same time. Great Minds!

[RandyRoberts]

Could you Group the report by Project, collapse the grouping, and then summarise each "count" column by sum? 

This would display the sum of each column at the top of the group.

TIP: if this solves your problem, I invite you to consider marking it as a Correct Answer to help others who might also find it of use.
If you like my content, please take a moment to view and vote on my Idea Requests: https://tinyurl.com/mysocalledideas

[skyehansen]

Focusing on your charts question: I don't think you will be able to get all 6 counts into one pie or bar chart. The closest you'll get within workfront is 6 charts on one dashboard (one for each type of menu item). Hopefully it makes sense if I give you the analogy of making a pie chart out of project statuses. A project only has one status field, so when you make a pie chart, you're reporting a bunch of different statuses in your pie, but it is coming from one field. This thing you are trying to do is coming from 6 fields. There's no way to combine it into one field because you have 6 answers. Normally I would be trying to locate an answer using a matrix report, but if you want a visual chart I think this is just out of the question.

If you make them horizontal bars, this will probably produce the best result. 

[EdwinSm]

Yes! You can create a calculated column that sums up the total occurrences of each value across all projects. Here's how you can approach it:

Solution: Adding a Total Count Column

You need to sum up each Value Count column across all rows to get a total count of each unique value.

Steps to Achieve This in a Report:

1. Create a Summary Report:

   • Group the report by none (so you get a total count across all projects).
   • Use aggregations (SUM function) for each Value Count column.

2. Use a Calculated Column (if available in your system):

   • If your reporting tool supports formulas, create a calculated column like:
     mathematica

     CopyEdit

     Total A1 Count = SUM(A1 Value Count) Total A2 Count = SUM(A2 Value Count) Total B1 Count = SUM(B1 Value Count) Total B2 Count = SUM(B2 Value Count) Total C1 Count = SUM(C1 Value Count)
   • This will aggregate all occurrences of each value across projects.

3. Generate a Pie Chart:

   • Once you have the total counts, plot them in a pie chart.
   • Each slice of the pie will represent how many times each value appears across all projects.

Expected Totals for Your Example Data:

Value Total Count

A1	2
A2	2
B1	2
B2	3
C1	2

If you’re using Text Mode Reporting, you may need to manually specify the column aggregation as a SUM function.

Would you like help writing a specific formula based on the platform you’re using (e.g., Workfront, Excel, Power BI)?