GROSS PROFIT

[#1]

I have more than 100,000 lines showing invoice data. Few lines are shown below.

Invoice NO	Purchase Amount	Sold Amount
INV001	100	125
INV002	125	130
INV003	140	148
INV004	150	165
INV005	200	250

I want table as below

Invoice NO	Purchase Amount	Sold Amount	% Profit
INV001	100	125	20.00%
INV002	125	130	3.85%
INV003	140	148	5.41%
INV004	150	165	9.09%
INV005	200	250	20.00%
TOTAL	715	818	12.59%

Kindly help me.

[#2]

Dear Anand, Thanks for your reply by sample attachment.

Kindly note that the Total % Profit should be 12.59% and not 58.34% shown in your example.

Dear Erika, kindly use sample shown by Anand.

Again, thanks for your response.

[#3]

Hi

Try something like....

[its_anandrjs]

Hi,

See the attached new updated file.

Regards

Anand

[#5]

Dear Anand,

Thanks for your quick response. But the total for % profit you shown is actually AVERAGE of % Profit. I need total of % profit which should be :  (818-715)/818 = 12.59%.

[#6]

Dear Gabriel,

Good Try, but this will not solve my problem as I want total at bottom of % Profit column.

Also, it will unnecessary take more time for script to execute as I have 100,000 records....

[#7]

I am on personal so I can not see the sample data.

Try adding a dimension as ='Total'

Change the style of the pivot to indent mode.

make the expression for percent:

if(left(Invoice,3)<>'INV', (sum([Sold amount])-sum([Purchase Amount]))/sum([Sold Amount]),

Sum(([Sold amount]-[Purchase Amount])/[Sold Amount]))

This should tell it for the dimensions invoice to use the previous expression that worked, but if it is on the total of the new dimension total, to use the sum of each.

Give that a try and see if it works.

[its_anandrjs]

Hi,

I got it see my next update file there is a litle trick but values are accurate i use this formula.

See the updated one.

Reagrds,

Anand

[#9]

Dear Anand,

Understood your trick but need right answer... LOL....

[#10]

is absolutely necessary to show the end of the column of profit? you can display on atable without dimension, only with expression

regards

[its_anandrjs]

Hi,

It is a trick by the way you can use like this.

Regards

Anand