# How to calculate a percentage on a Summary Report?

• 0
• Question
• Updated 2 months ago
• In Progress
I have a table to track work tickets.  I want to be able to run a monthly summary report which shows the percentage of work tickets completed by type.  Basically just [completed tickets]/[total tickets].  If I add a formula field into the table I can see an accurate percentage for each day, but if I run a monthly summary report the percentage is not accurate no matter how I tweak the different options.

For example, lets say I have three types of tickets ... Landscape, Maintenance and Misc.  Everyday there will be tickets opened and closed for each type.  So for one day it might look like this:

Type                    Opened Tickets     Closed Tickets
Landscape          22                          8
Maintenance       12                          6
Misc                    15                          0

Everyday will have different totals.  At the end of the month I want a summary report which calculates the percentage of the three ticket types.  What I want is very simple although it probably seems complicated the way I am explaining it.
• 70 Points

Posted 3 months ago

• 0

Evan Martinez, Community Manager

• 12,318 Points
Hi William,

In order to get those calculations for the average across all of your records instead of getting a total of all the averages for each individual records you actually need to create a summary/statistics table and use a relationship to bring up both of those numbers as a summary field in order to do math on the total values. In your instance you would need a table along the lines of Monthly Ticket Types with records that contain a text field set up for each possible combo you want to report off like:

Monthly Combo Field:
January-2019-Landscape
January-2019-Maintenance
January-2019-Misc
February-2019-Landscape
February-2019-Maintenance
February-2019-Misc
March-2019-Landscape
March-2019-Maintenance
March-2019-Misc

Then you would want that tables key field to be set to the Monthly Combo field you created and you have filled it with records to match what you want to report off of you are ready to add a formula field in your Tickets table called something like Monthly Combo Formula, and fill it with a formula that will take the date, the year, and the ticket type and put them together in a format like the records that exist on your Monthly Ticket Types table. That should fill each record in with its matching Monthly Ticket Type record. Then you can create a relationship between the two tables where Monthly Ticket Types have many Tickets and the related record field is your Monthly Combo formula.

Once this is all set up then you can make formula fields on your Monthly Ticket Type table and do the math right off of your summary fields. If in the future you ever need to add new summary fields you would just do the same step of making the field a text field, add the summary field, and then turn it back into the formula. This is definitely a more advanced technique and can take some maintenance but it is the way to get to a summary calculation like that. I hope this information is helpful.
• 80 Points
Hi Evan,

First of all, thank you for taking the time to write such a detailed reply.  I really appreciate it.  Wow, this seems much more complicated than it should be.  It makes me wonder if I didn't word the question correctly.  Let me just recap:

I have a summary report which is set to run on the first day of each month.  The filter is all tickets opened in the previous month.  So for this month the report might look like this.

Type                    Opened Tickets     Closed Tickets
Landscape          318                          287
Maintenance       91                            69
Misc                    106                          80

All I want is another column with the percentage of tickets closed [Closed Tickets]/[Opened Tickets].  Is the method you describe really the best way to do this?

Evan Martinez, Community Manager

• 12,318 Points
Hi William,

Currently yes that is the way you would set your app up in order to pull the percentages based off of all of your closed tickets divided by all of your opened tickets. Summary reports summarize up all the data across records but they do not have the ability to run calculations off of the summary values displayed. Calculated columns in the report instead run the formula off of individual records, which means the math doesn't come out the same.

A relationship allows you to control how the data is aggregated before the math is done so you can control how you want it to all collect and then run the formulas on the summary fields. This then can give you a percentage based off something like a Type and Date combo.