I have two dimension lists: one for my P&L accounts and one for my balance sheet accounts. Both of them are updated with new accounts once they appear in the P&L transaction list or the balance sheet transaction list.
For reporting purposes, I want to create a single chart of accounts dimension list that contains all the items from the P&L and balance sheet dimensions lists.
Is there are way to combine both dimension lists into one and to do that in such a way that it automatically updates once a new P&L or balance sheet account appears in either of the two transaction lists? By the way; both dimension lists currently do not share the same properties. So I would simply be looking to combine the unique values/dimensions from each list and append them together as a new total chart of account list.
Thanks for helping me out :)
Best, Bas
Page 1 / 1
Hi,
There are probably different and better ways to achieve this, but a simple way I use is attributing IDs to your items and then calling them in the new dimension.
I’ve answered a similar question recently:
I created a short model to illustrate it:
Dim A and Dim B are the two source dimensions, Since they’re dimensions, all items within them are already unique, but I assumed they can have common items (Green and White).
Dim A.ID is a auto-generated integer unique property.
Dim B.’exclude duplicates’ checks if the Dim B.Name already exists or not in Dim.A to keep only the unique items.
Dim B.ID is a cumulation that starts after 100, considering that Dim A won’t be larger that 100 items, but you can add your own limit
Then on the Joined Dimension, I added 200 items and Joined Dimension.ID is a auto-generated integer unique property.
Then Joined Dimension.Name is just matching the IDs with Dim A and Dim B.
Note on the Joined Dimension:
Using this technique, the Joined Dimension could contain a lot of blank items (IDs with no match), in my example the Joined Dimension view is filtered on “non blank” items, and later on your model they would not have data so using the “Hide empty rows and columns” filter will also hide the useless blank Joined Dimension items.
Note on new accounts imported:
Any new item added to the 2 source dimensions will create a new unique ID, and then be added in the Joined dimension. So it automatically updates once a new P&L or balance sheet account appears in either of the two source transaction lists.
Hope this helps.
Cant you just do the reverse ?
E.g. have a full list of chart of accounts, then subset it into 2 subsets : 1 with pnl , 1 with balance sheet.
Usually coa tables are held outside of epm system, so usually all accounts are all in the same place for accounting
