irfananeeza
Active Member
- Joined
- Feb 15, 2008
- Messages
- 334
- Office Version
- 2010
Hi Experts,
I am having some trouble with my pivot table. I have added a simple formula using calculated items in the pivot table. When the formula shows up in the pivot table, the row label also get repeated multiple times for no reason.
CURRENT SOURCE DATA
SYSTEM|FUND ID|LEVEL1|LEVEL2|LEVEL3|AMOUNT
ESSBASE SYSTEM|FUND ID|Balance Sheet|Net Assets|Total Assets|$$
ESSBASE SYSTEM|FUND ID|Balance Sheet|Net Assets|Total Liabilities|$$
ESSBASE SYSTEM|FUND ID|Income Statment|Incr(Decr) in NA|Total Income|$$
ESSBASE SYSTEM|FUND ID|Income Statement|Incr(Decr) in NA|Total Expenses|$$
MPOWER SYSTEM|FUND ID|Balance Sheet|Net Assets|Total Assets|$$
MPOWER SYSTME|FUND ID|Balance Sheet|Net Assets|Total Liabilities|$$
MPOWER SYSTEM|FUND ID|Income Statment|Inc(Decr) in NA|Total Income|$$
MPOWER SYSTEM|FUND ID|Income Statement|Incr(Decr) in NA|Total Expenses|$$
PIVOT TABLE LAYOUT (WITHOUT FORMULA)
REPORT FILTER | FUND ID
COLUMN LABELS | SYSTEM
ROW LABELS | LEVEL1/LEVEL2/LEVEL3
VALUES | SUM OF AMOUNT
Piovt works perfectly fine without adding a formula.
PROBLEM:
The problem starts, when I added a caluclated item field in my pivot table to calculate variance between Essbase and Mpower (i.e. Essbase-Mpower). I noticed that all of a sudden, pivot table row labels are repeated i.e. Income Statement data also rolls up to Balance sheet and similarly Balance Sheet data is also rolling under Incoem Statement as well. Even though Balance Sheet data contains no information for Income Statement and vice versa. Here is how my pivot table looks like...
PIVOT TABLE (WITH FORMULA)
REPORT FILTER | FUND ID-1234
ROW LABELS ..............................................................|VALUES
SUM OF AMOUNT...........................................................|SYSTEM
LEVEL 1............| LEVEL 2 .................| LEVEL 3................|LAWSON | MPOWER | VARIANCE FORMULA(lawson-mpower)
BALANCE SHEET .| NET ASSETS .........| TOTAL ASSETS |$$
.......................| ............................| TOTAL LIABILITIES|$$
.......................| Net Incr(Decr) in NA | TOTAL INCOME |$$
.......................| ........................... | TOTAL LIABILITIES|$$
INCOME STATMNT|NET ASSETS..........|TOTAL ASSETS......|$$
........................|...........................|TOTAL LIABILITIES..|$$
........................|Net Incr(Decr) in NA|TOTAL INCOME.......|$$
........................|...........................|TOTAL EXPENSES |$$
As you can see the problem. Could you please help me explain why adding a calculated item formula causing this error i.e. why balance sheet shows Total income /expenses when it is not related to balance sheet. And why Income statement shows Total Assets/Liabilities even though it is not set up that way in the source sheet. Your help would be helpful.
Thanks.
I am having some trouble with my pivot table. I have added a simple formula using calculated items in the pivot table. When the formula shows up in the pivot table, the row label also get repeated multiple times for no reason.
CURRENT SOURCE DATA
SYSTEM|FUND ID|LEVEL1|LEVEL2|LEVEL3|AMOUNT
ESSBASE SYSTEM|FUND ID|Balance Sheet|Net Assets|Total Assets|$$
ESSBASE SYSTEM|FUND ID|Balance Sheet|Net Assets|Total Liabilities|$$
ESSBASE SYSTEM|FUND ID|Income Statment|Incr(Decr) in NA|Total Income|$$
ESSBASE SYSTEM|FUND ID|Income Statement|Incr(Decr) in NA|Total Expenses|$$
MPOWER SYSTEM|FUND ID|Balance Sheet|Net Assets|Total Assets|$$
MPOWER SYSTME|FUND ID|Balance Sheet|Net Assets|Total Liabilities|$$
MPOWER SYSTEM|FUND ID|Income Statment|Inc(Decr) in NA|Total Income|$$
MPOWER SYSTEM|FUND ID|Income Statement|Incr(Decr) in NA|Total Expenses|$$
PIVOT TABLE LAYOUT (WITHOUT FORMULA)
REPORT FILTER | FUND ID
COLUMN LABELS | SYSTEM
ROW LABELS | LEVEL1/LEVEL2/LEVEL3
VALUES | SUM OF AMOUNT
Piovt works perfectly fine without adding a formula.
PROBLEM:
The problem starts, when I added a caluclated item field in my pivot table to calculate variance between Essbase and Mpower (i.e. Essbase-Mpower). I noticed that all of a sudden, pivot table row labels are repeated i.e. Income Statement data also rolls up to Balance sheet and similarly Balance Sheet data is also rolling under Incoem Statement as well. Even though Balance Sheet data contains no information for Income Statement and vice versa. Here is how my pivot table looks like...
PIVOT TABLE (WITH FORMULA)
REPORT FILTER | FUND ID-1234
ROW LABELS ..............................................................|VALUES
SUM OF AMOUNT...........................................................|SYSTEM
LEVEL 1............| LEVEL 2 .................| LEVEL 3................|LAWSON | MPOWER | VARIANCE FORMULA(lawson-mpower)
BALANCE SHEET .| NET ASSETS .........| TOTAL ASSETS |$$
.......................| ............................| TOTAL LIABILITIES|$$
.......................| Net Incr(Decr) in NA | TOTAL INCOME |$$
.......................| ........................... | TOTAL LIABILITIES|$$
INCOME STATMNT|NET ASSETS..........|TOTAL ASSETS......|$$
........................|...........................|TOTAL LIABILITIES..|$$
........................|Net Incr(Decr) in NA|TOTAL INCOME.......|$$
........................|...........................|TOTAL EXPENSES |$$
As you can see the problem. Could you please help me explain why adding a calculated item formula causing this error i.e. why balance sheet shows Total income /expenses when it is not related to balance sheet. And why Income statement shows Total Assets/Liabilities even though it is not set up that way in the source sheet. Your help would be helpful.
Thanks.
Last edited:
