Express Total Margin within Straight Table

[#1]

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	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%

Thanks,

Paul

[maxgro]

(sum([Nett Price]) - sum(Cost)) / sum([Nett Price])

[#3]

Thanks Massimo,

This works well, the only issue I have is that my Nett price is derived from a calculation of ‘List Price less Discounted Value’.

Do I need to include the calculated fields in the formula below, so something like...

(sum() - sum(Cost)) / sum()

Regards,

Paul

[maxgro]

I think

(sum([List Price] - [Discounted Value]) - sum(Cost)) / sum([List Price] - [Discounted Value])

another way

if you have a calculated field in your chart you can use it in the expressions in same chart using the label of that field

[#5]

Thanks for your response.

I have revisted my table but still have a few issues.

I have two input fields created, one for quantity and one for discount, The values for List Price and Cost are created by multiplying the input fields.

Should it be possible to see a calculated total margin, if input fields are contained within the expression?

Thanks,

Paul

[timsaddler]

Hello

Putting the calculation for margin value into the script may help the data to roll up correctly?

[#7]

Hello Everyone,
                      I am working with Paul on this tool as well. The problem we are encountering is that we are 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 Tim, can you please elaborate on what should be done in the script breifly.

Thanks,

Zarar

[timsaddler]

Put the calculation into the script instead of the Expression box. Then the roll up should work.

[timsaddler]

To explain better... here's an example from one of my scripts...

"Extended Price" as SalesExcLevy,

     "Aggregate Levy" as AggLevy,

     "Extended Price" + "Aggregate Levy" as SalesIncLevy,

     "Std Cost Haulage" as 'StdCostOfHaulage',

     "Split_Std_Cost_Of_Haulage" as 'SplitStdCostOfHaulage',

     "Actual_Cost_of_Haulage" as 'ActCostOfHaulage',

     ("Extended Price" + "Aggregate Levy") - "Std Cost Haulage" as StdExWorksSalesIncLevy,