i need help with a pivot table:
I need to separate in dimension:
GROSS PROFIT: SALES + COST
RUNNING EXPENSES: DIMENSION
EBIT: GROSS PROFIT + RUNNING EXPENSES
How could I do this in a pivot table?
You can use valuelist for cases where custom dimensions are required. See below
But in your case why do you need custom dimensions? You can use a standard pivot table
you can put all 6 as expressions. your site (the 1,2s) as the dimension
e.g. like below
and drag the expressions into rows and dimension as a column to create below
But if i missed something. value list would be the way to go
tks for info Dilip.
I was thinking about that but the problem is that if I add a sub group dimension, it's not possible to pivot.
do you have a solution for this?
this is ok:
i need to pivot: collapse sub_group_eng
Ah ok. then you might need the value list option which i shared before.
basically you create custom dimensions on the fly and within a single expression for each 'dimension' like Sales you give the formula for each one.
let me know if you have troubles
Below holds if your facts are additive, so Gross Profit =Sales minus Cost of Sales per my accountancy text book, but what you have is fine as long as the signs are appropriate on the facts.
So an accounting system would hold a mapping of low level account codes through a hierarchy to EBIT, similar to;
|Level 1||Level 2||Level 3||Level 4|
|GROSS PROFIT||SALES||eg Sales Income|
|EBIT||GROSS PROFIT||COST||eg Rent|
|EBIT||RUNNING EXPENSES||RUNNING EXPENSES||eg Marketing|
So I would construct a table like the above with Level 4 linking through to your facts & use the Level 1-3 I the pivot table with summary totals where appropriate.
If your user has very fixed ideas that this should look exactly like Excel then you may have a struggle on your hands , maybe they need Power Pivot .
Let me see if I can get close, but I will give up if it is fighting too hard.