Skip to main content
Question

Margins on a sales report Totaling

  • January 9, 2020
  • 0 replies
  • 57 views
Forum|alt.badge.img+11
I have a table report that shows each of my projects with several columns:

Project Name | Total Sell | Total Cost | Total Profit | Margin

Each project might have several rows (due to the original contract + change orders)
I have the table to sort and group by project name and it subtotals each project so I can get the total current sell price, total cost, and total profit aggregated for the project.  

This does not work for Margin.  By default it was summing the margin column, of course wrong.  Let's say I have 30 rows on this project and they are all at a 20% margin for easy math.  The total would result in 60% margin because they are adding, incorrect.

I tried switching it to average but that is also incorrect.  Let's say the revenue is disproportionate and the margins are not even on all of the rows.  It would take the total margin and divide by 3 (in the prior example).  That is not correct as if one row is 3,000,000 at 20% and the next two rows are much smaller values but at lets say 30% the average is not the (3) totaled and divided by 3.  It is the total profit / total sell.

Is there a way to get the total margin the table self generates to run the formula of the total profit / the total sell?  That is the only way to get an accurate value in there.

------------------------------
Ivan Weiss
------------------------------

MarkShnierYou
Forum|alt.badge.img+22
  • MarkShnierYou
  • Quickbase Alumni
  • January 9, 2020
There is not currently a way to do math between totals on a summary report or on totals on a table report.  You would need to get those totals accumulated up to a parent record and then do the math there.  The issue of course is that you need to plan out what totals you need to accumulate and set up the Parent table records. 

For example if you just wanted monthly totals with Margins, you would need to load up a table of months where the Key field is the first of each month and then make a relationship down to the detail tables and roll up the total sales and total costs up to that record, then do proper margin calculations up there.

mnark

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
Mark Shnier. Your Quickbase Coach
  • JasonJohnson2
  • Quickbase Alumni
  • January 9, 2020
I keep my changes in a Change Log table and put a table report in the Project form to show the margins on change orders. I then show the original margin , current projected margin (w/changes) and the actual margin. 1 line per project and in a table not a summary report with the average at the bottom.  I made sure to put the financial details on one of the first few tabs to keep it simple to find for management when they want to see the details.
This issue was a headache for me as well.

------------------------------
Jason Johnson
------------------------------
Terms & ConditionsAccessibility statement