This discussion is archived
4 Replies Latest reply: Sep 18, 2012 12:36 AM by 962672

# Need customerized formula in pivot table

Currently Being Moderated
Hi all,
This is my first post on the Oracle Forum, so say hi to everybody.

I have a user requirement, that is:
we got users oragnization like this: region, location
we got users data like sales, profit
in the table view i can perform a custormerized formula that is: profit/sales*100, so we got gross profit margin.

The question is based on:

If i need to do some total calculation such as total location's sales or profit, i need to use a pivot table,
i can easily draw the sales and profit column as my measures but i could not draw my gross profit margin to my measure,
because pivot table will do a total sum calculation.

My question is:

We all know that it's easy to perform a custormerized formula in MS-Excel pivot table. But i could not find such function in BIEE 10g.
So, could anyone give me some suggestions?

PS, the user_guide doc did not have a example as my need. Need experts help!

Edited by: user4018817 on 2012-9-19 上午12:31
• ###### 1. Re: Need customerized formula in pivot table
Currently Being Moderated
On Criteria tab pull columns region,location,sales,profit and profit gain
then click on fx for the last column profit then add / then click profit from navigation so that your formula match to
profit/sales*100

then create a pivot table as per requirements.

Hope this helps

Pls mark if helps.
• ###### 2. Re: Need customerized formula in pivot table
Currently Being Moderated
thx a lot.
but if we do this the gross profti margin will do a sum calculation.
for example:

region location sales profit margin
100 1 100 50 50
200 1 100 25 25

if do the exact as you say

we only got this:

column
row: location sales profit margin
1 200 75 75

the margin is not equal 75/200*100 = 37.5
• ###### 3. Re: Need customerized formula in pivot table
Currently Being Moderated
Hi,

change the aggregation of profit margin column to average,

it should work,

Cheers
-MT
• ###### 4. Re: Need customerized formula in pivot table
Currently Being Moderated
May be my example is not proper.
You see this:

sales profit margin
a b b/a
c d d/c

we do not always get this:

(b+d)/(a+c) = (b/a+d/c)/2

So... i need the fomula like this (b+d)/(a+c).

#### Legend

• Correct Answers - 10 points