Power Pivot: Make sum's of General Ledger accounts

[MrLynge]

Hi,

I need to make a pivot of a general ledger.
My problem is that since the amounts are stored on the G/L account entry's and amounts for subtotals like "Total sales" or "Total assets" are sum's that do not have their own accounts I get blank values for these "sum-accounts".
I have tried to make measures or calculated columns, but I can't get anything to sum all amounts from account X to account Y.
Ex. to calculate total sales i made the following measure: =CALCULATE(SUM('G_L Entry'[Amount]);'G_L Entry'[Number]<12199). But of course this only gives the amounts for my accounts less the 12199 - I need it to be a sum of the amounts for the number values less the 12199.

Without DAX this would be an easy fix but can this be done in a DAX context?

I guess someone must have had the same wish before me, so any experience would be appreciated.

/MrLynge

 

[gazpage]

http://www.daxpatterns.com/static-segmentation/

try this pattern.

 

[akice]

Another approach (and the one i took) was to take the chart of accounts, put in an excel worksheet, and assign a row code to each one. The row codes are another table you create for the mapping to a financial statement section (Cash = 100, Inventory = 150, Sales = 500, OH expenses = 800, whatever). Go as granular as you want. Then import the mappings to the data model and establish relationships. From there, to get "Total Sales" it is a simple CALCULATE ( SUM( 'G_L Entry'[Number]; Financial_Sections[RowCode] = 500 ). Of course where it gets tricky are the balance sheet accounts which are LTD, whereas SOO are YTD. From there you'll maybe need a special calculation for RE. My accounting package includes a summary view with beginning balances so I didn't have to figure all that out. It ended up being easy for me. Hope this helps....

 

[MrLynge]

http://www.daxpatterns.com/static-segmentation/

try this pattern.

_____

Hi Gazpage,

Thanks for your response.

I'll have a look at it and revert to you!

 

[MrLynge]

Another approach (and the one i took) was to take the chart of accounts, put in an excel worksheet, and assign a row code to each one. The row codes are another table you create for the mapping to a financial statement section (Cash = 100, Inventory = 150, Sales = 500, OH expenses = 800, whatever). Go as granular as you want. Then import the mappings to the data model and establish relationships. From there, to get "Total Sales" it is a simple CALCULATE ( SUM( 'G_L Entry'[Number]; Financial_Sections[RowCode] = 500 ). Of course where it gets tricky are the balance sheet accounts which are LTD, whereas SOO are YTD. From there you'll maybe need a special calculation for RE. My accounting package includes a summary view with beginning balances so I didn't have to figure all that out. It ended up being easy for me. Hope this helps....

____

Hi Akice,

Thanks for your reply.

Great idea with the mapping. Ideally I would like it to be a variable solution that captures new accounts by itself, but your proposal might be the only practically possible one .
A variable solution might be too cumbersome, and if so this will definitely work. Thanks again!

 

[MrLynge]

_____

Hi Gazpage,

Thanks for your response.

I'll have a look at it and revert to you!

_____

Hi again Gaspage,

Now I have hat a look at it and I now remember reading about this before. It think I might be able to solve my problem using this method. Again thanks a lot!

 

[gazpage]

_____

Hi again Gaspage,

Now I have hat a look at it and I now remember reading about this before. It think I might be able to solve my problem using this method. Again thanks a lot!

The method that Akice suggested is clearly the 'normal' way to do this, with a one to many relationship to aggregate the numbers. The static segmentation pattern allows you to effectively do the same thing for a range of values, but is more complex and probably slower.

 

[MrLynge]

The method that Akice suggested is clearly the 'normal' way to do this, with a one to many relationship to aggregate the numbers. The static segmentation pattern allows you to effectively do the same thing for a range of values, but is more complex and probably slower.

____

I ended up solving the issue by applying Segmentation. Both your posts was a huge help though. Thanks again!

 

[akice]

Segmentation is a fine approach as long as you are extremely disciplined in creating gl's; but if you are not, you end up with a bunch of exception code like "> 100 AND < 200, except for 127, 163 & 184 which we want to split out, or include in different buckets". You can then end up with values included 2x's and you spend a bunch of time tracking down out of balance issues. For me, we rarely add gl's (only a handful a year) plus I store the mapping in a GL Master user defined field in the accounting software. It makes it super easy to check for missing accounts. So a definite trade off. I found my approach works best for me. Glad yours is working for you.