# Power Pivot: Make sum's of General Ledger accounts



## MrLynge (Nov 29, 2017)

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 (Nov 29, 2017)

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

try this pattern.


----------



## akice (Nov 29, 2017)

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 (Nov 30, 2017)

gazpage said:


> 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 (Nov 30, 2017)

akice said:


> 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 (Nov 30, 2017)

MrLynge said:


> _____
> 
> Hi 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!


----------



## gazpage (Nov 30, 2017)

MrLynge said:


> _____
> 
> 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 (Nov 30, 2017)

gazpage said:


> 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 (Nov 30, 2017)

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.


----------

