Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Let say I havea pivot table like :
Product1 Product2 Product3
Nb Sales Amount Nb Sales Amount Nb Sales Amount
Sales Man
John 2 20 3 23 5 500
Mark 4 30 3 15 3 350
I would like to add a column that does the following calculation
Product n / Product 1 To get a ratio.
Does anybody has a solution ?
Thanks
Philippe
When you can be sure that Product 1 is in column 1 you can use the following expression:
Sum(Amount)/First(Sum(Amount),1)
This results in:
Product | Prod1 | Prod2 | Prod3 | ||||||
---|---|---|---|---|---|---|---|---|---|
SalesMng | NbSales | Amount | Prod n/Prod 1 | NbSales | Amount | Prod n/Prod 1 | NbSales | Amount | Prod n/Prod 1 |
John | 2 | 20 | 1,0 | 3 | 23 | 1,1 | 5 | 500 | 25,0 |
Mark | 4 | 30 | 1,0 | 3 | 15 | 0,5 | 3 | 350 | 11,7 |
Total | 6 | 50 | 1,0 | 6 | 38 | 0,8 | 8 | 850 | 17,0 |
I hope this is what you mean
When you can be sure that Product 1 is in column 1 you can use the following expression:
Sum(Amount)/First(Sum(Amount),1)
This results in:
Product | Prod1 | Prod2 | Prod3 | ||||||
---|---|---|---|---|---|---|---|---|---|
SalesMng | NbSales | Amount | Prod n/Prod 1 | NbSales | Amount | Prod n/Prod 1 | NbSales | Amount | Prod n/Prod 1 |
John | 2 | 20 | 1,0 | 3 | 23 | 1,1 | 5 | 500 | 25,0 |
Mark | 4 | 30 | 1,0 | 3 | 15 | 0,5 | 3 | 350 | 11,7 |
Total | 6 | 50 | 1,0 | 6 | 38 | 0,8 | 8 | 850 | 17,0 |
I hope this is what you mean
Thanks a lot.
That does exactly what I need.
Philippe