How to calculate a percentage on a Summary Report?

Question

I have a table to track work tickets.&nbsp; I want to be able to run a   monthly summary report which shows the percentage of work tickets   completed by type.&nbsp; Basically just [completed tickets]/[total tickets].&nbsp;   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.&nbsp; Everyday there will be tickets opened and closed   for each type.&nbsp; So for one day it might look like this:Type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Opened Tickets&nbsp;&nbsp;&nbsp;&nbsp; Closed TicketsLandscape&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8Maintenance&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6Misc&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0Everyday   will have different totals.&nbsp; At the end of the month I want a summary   report which calculates the percentage of the three ticket types.&nbsp; What I   want is very simple although it probably seems complicated the way I am   explaining it.

evanmartinez · Answer

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-LandscapeJanuary-2019-MaintenanceJanuary-2019-MiscFebruary-2019-LandscapeFebruary-2019-MaintenanceFebruary-2019-MiscMarch-2019-LandscapeMarch-2019-MaintenanceMarch-2019-MiscThen 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.&nbsp;Then once it looks like everything is set up correctly when you look at records and they seem to be correctly related you want to go into Monthly Combo formula then save your formula before setting the field to be just a plain text field instead of a formula. Once you do that your records are related and you can then go into your relationship between Tickets and Monthly Ticket Types and you can add summary fields to summarize the number of open tickets and closed tickets. Then once you have that set up you would go back into Monthly Combo&nbsp;and turn it back into a formula field and make sure your formula is still in there. Then any new records added will link up so long as there is a matching record added into the summary table. Then at that point to maintain your summary table you just need to make sure you add records for every combo you want to track, that usually means once a month adding a record for each type that month.&nbsp;Once this is all set up then you can make formula fields on your Monthly Ticket Type&nbsp;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.&nbsp;

william · Answer

Hi Evan,First of all, thank you for taking the time to write such a detailed reply.&nbsp; I really appreciate it.&nbsp; Wow, this seems much more complicated than it should be.&nbsp; It makes me wonder if I didn't word the question correctly.&nbsp; Let me just recap:I have a summary report which is set to run on the first day of each month.&nbsp; The filter is all tickets opened in the previous month.&nbsp; So for this month the report might look like this.Type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Opened Tickets&nbsp;&nbsp;&nbsp;&nbsp; Closed TicketsLandscape&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 318&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 287Maintenance&nbsp; &nbsp; &nbsp; &nbsp;91&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 69Misc&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 106&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 80All I want is another column with the percentage of tickets closed [Closed Tickets]/[Opened Tickets].&nbsp; Is the method you describe really the best way to do this?

evanmartinez · Answer

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.&nbsp;

barbaraalvarez1 · Answer

I know this thread is quite old, but I needed the same type of logic that the OP was looking for. I followed the instructions and it worked to a tee except when changing my text field back to a text formula. The error says you cannot have a formula field as a summary field in a relationship. Is this a new rule since this thread was written? Is there a way to get around that?

Thank you

------------------------------
Barbara Alvarez
------------------------------

markshnier__you · Answer

Barbara,This is, as you said, &nbsp;a very old thread.&nbsp;
There are newer and potentially much easier solutions now. &nbsp; I suggest that you post a new question, perhaps referencing this post but still fully describing your situation and question and myself and others will watch for it.&nbsp;
You may be able to solve this simply using what are called summary formula fields in a Summary Report Type.&nbsp;------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

Forum Discussion

How to calculate a percentage on a Summary Report?

5 Replies

Related Content

How to Calculate Completion Percentage Using Multi-Select Boxes

Percentage through the month

Calculation/Performance Limits Inquiry

Calculating 1.5 pay

Formula to Calculate percentage based on Checkmarks and dates