Hi everyone,
I have a straight table which calulates gross margin per line.
When multiple lines are selected, it dispays the average % margin - 41%
How do you express the totals line to show the margin for the total value - in this case (Nett-Cost) / Nett, which should be 39%
| Item | £ List Price | Nett Price | £ Cost | Margin % |
| A | 5,000 | 2,000 | 1,000 | 50% |
| B | 3,000 | 2,000 | 1,000 | 50% |
| C | 4,000 | 3,000 | 1,500 | 50% |
| D | 2,000 | 1,900 | 1,500 | 21% |
| E | 1,000 | 500 | 300 | 40% |
| F | 500 | 400 | 200 | 50% |
| G | 5,000 | 1,000 | 500 | 50% |
| H | 5,000 | 4,000 | 2,900 | 28% |
| I | 4,000 | 1,500 | 1,000 | 33% |
| Total | | 16,300 | 9,900 | 41% |
The problem I am encountering is that I am not getting the total margin % at the bottom of all the individual margins. If I select 'Expression Total', it doesn't return any value but a '-'. If I select Sum of Rows, it literally adds up all the numbers which is not correct either. Selecting Average of Rows gives us the the Avg which is near to but still not correct Margin.
The formula for margin is : ''([Total Net Price]-[Total Cost])/[Total Net Price]'' where Total Net Price and Total Cost are also formulae. I heard that the Aggregation 'AGGR' function might be of help. So can anyone please guide me through AGGR function.
And if it can be done any other possible way, can anyone please guide me through that as well.
Thanks,
Zarar
