Margins on a sales report Totaling
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
------------------------------
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
------------------------------
