Results 1 to 9 of 9

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

  1. #1
    New Member
    Join Date
    Nov 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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


    Power Pivot: Make sum's of General Ledger accounts
    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

  2. #2
    Board Regular
    Join Date
    Apr 2015
    Posts
    371
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Pivot: Make sum's of General Ledger accounts


  3. #3
    Board Regular
    Join Date
    Apr 2016
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Pivot: Make sum's of General Ledger accounts

    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....

  4. #4
    New Member
    Join Date
    Nov 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Pivot: Make sum's of General Ledger accounts

    Quote Originally Posted by gazpage View Post
    _____

    Hi Gazpage,

    Thanks for your response.

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

  5. #5
    New Member
    Join Date
    Nov 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Pivot: Make sum's of General Ledger accounts

    Quote Originally Posted by akice View Post
    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!

  6. #6
    New Member
    Join Date
    Nov 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Pivot: Make sum's of General Ledger accounts

    Quote Originally Posted by MrLynge View Post
    _____

    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!

  7. #7
    Board Regular
    Join Date
    Apr 2015
    Posts
    371
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Pivot: Make sum's of General Ledger accounts

    Quote Originally Posted by MrLynge View Post
    _____

    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.

  8. #8
    New Member
    Join Date
    Nov 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Pivot: Make sum's of General Ledger accounts

    Quote Originally Posted by gazpage View Post
    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!

  9. #9
    Board Regular
    Join Date
    Apr 2016
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Pivot: Make sum's of General Ledger accounts

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •