How do I get the sum of column as a % of the sum of another column in a summary report?

  • 0
  • 1
  • Question
  • Updated 7 months ago
  • In Progress
  • (Edited)
I have two field one contains the customer Spend by line item the second is the amount of Savings in that same transaction.  The savings is not always the same % and in some cases there is no savings.

I need to be able to create a summary table that shows the total of what the customer spent in a given month (Spend field), how much the saved in a given month (Savings field), and finally the % Saved.  This is my problem.  In Excel the formula would be Sum(Column Savings)/Sum(Column Spend) = % Savings.  I also need to use this same % in several other reports and charts such as a Bar and Line where the Spend is the bar and the line is the % Savings.

Photo of Scott

Scott

  • 238 Points 100 badge 2x thumb

Posted 7 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
At present, Quick Base does not support math operations against columns on a Summary report, although the question does come up pretty often on this forum.

Depending on your table structure it is sometimes possible to get around this limitation with another table where the data can be summarized up to, but I see in your case, that other table would need to have every possible combination of the Category Listing, the Spend Owner and the Month.
Photo of Scott

Scott

  • 238 Points 100 badge 2x thumb
So if i had a duplicate table to the main one with all the same fields in it I could potentially get the % figure.  I assume that would be by adding a summary field in the master table relationship to the second table?  That is entirely possible because the data come from an upload and would not be too difficult.  

How would I set that up and how would I incorporate it into my reports?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
You would need to upload a table with a concatenated field of the

Category Listing, the Spend Owner and the Month.  That field would be set to be the Key field of the table.


so like
Guaranted-TD-201801 
Guaranted-TD-201802
Guaranted-TD-201803
.
.
Guaranted-MRO-201801
Guaranted-MRO-201802
Guaranted-MRO-201803



Then  on the details table you would create that same field by formula and then use that field in a relationship to do the summaries of the total spend and total savings.

Then calculate your % up on the summary record.

Then look that % savings down to the details table.

Then shows that field in the summary report as an "Average".  Note that the left most column of the report for the grand total average will be not mathematically correct  and you will not be able to suppress it from the report.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
The thought stuck me driving into work that you can make the summary table entries by making a summary report in your details table of the unique values for the concatenated string and then and exporting it to the summary table.

Then do the reporting all off the summary table.