Power Pivot: Make sum's of General Ledger accounts

MrLynge

New Member
Joined
Nov 29, 2017
Messages
7
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

akice

Board Regular
Joined
Apr 18, 2016
Messages
180
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

New Member
Joined
Nov 29, 2017
Messages
7

ADVERTISEMENT

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

New Member
Joined
Nov 29, 2017
Messages
7
_____

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! :biggrin:
 

gazpage

Active Member
Joined
Apr 17, 2015
Messages
393

ADVERTISEMENT

_____

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! :biggrin:

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

New Member
Joined
Nov 29, 2017
Messages
7
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

Board Regular
Joined
Apr 18, 2016
Messages
180
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,811
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top