Totalling Ledger NAV

[Guustc]

I think the title is not clear at all, but it is difficult to give my problem 'a name'.

I have the following table (an example, of course there is more data in each row)

 GL Account Number - Account type - Totalling

10 - Account - 'empty'

15  - Account - 'empty'

20 - Account - 'empty'

25 - Total - 10..25

The problem is with the bold rows of which the account type = Total. For this rows the Totalling column is filled with the Gl account numbers that have to be summed up in order to get a Total balance. In NAV the '..' symbol means that it sums up the balance amount for the first number until and with the last number.

Now I want to filter on the data in the rows, based on the GL account numbers with the "Total" Type. So if i select 25, i get the amount of all the GL account numbers between and with 10 to 25. 

How can i get this fixed? I already splitted the column in 10 and 25 but i don't know how to go further. Anyone who can help?

The total rows also have a name like Administration costs or something, it is also good if i can fill every normal (GL account type = Account) with this name, so i can filter on that but i also can not get that to work.

[clausm73]

For a Power Query based solution to this problem - read this post:
Dynamics NAV account schedule

[Anonymous]

I had the same challenge and I think I solved it using a measure that dynamically calculates the sum without using calculated columns and it goes like this:

[GL amount] = SUMX('GL Account', 
    IF( NOT ISBLANK('GL Account'[Totaling Max]), 
        CALCULATE ( SUM('General Ledger'[Amount]) ), FILTER(ALL('GL Account'), 'GL Account'[Account Code] >= EARLIER('GL Account'[Totaling Min]) && 'GL Account'[Account Code] < EARLIER('GL Account'[Totaling Max])) ),
        CALCULATE ( SUM('General Ledger'[Amount]) ))
    )
)

Meaning it goes for every GL account, checks whether it is a Totaling account or not, and it performs the sum of all accounts that the totalling includes (10..25 - 10,11,12,...23, 24, 25), and for a 'simple' account it computes the sum of just that account from the GL Entry tables.

It works for me and rerturns the right numbers. Let me know what you think.

[oajayi]

Can you create a measure that sums up the Amount field of 5 selected accounts in on the G/L Entry table?

Thanks.

[oajayi]

Can you write this DAX Language if I just want the Total Amount for 5 selected GL Accounts e.g. 111001, 293028, 283746, etc

Thanks.

[v-sihou-msft]

@Guustc

As I understand it, you want to get a total amount from the first row to current row within same group if current row Account Type is “Total”. Right?

You can create another table, includes the “GL Account Number” whose type is “Total”. And then create a calculated column to sum up them with following steps:

1. Add an index column for the table in Query Editor.
   
2. Create a new table for the slicer.
   

   TotalTable = 
   SUMMARIZECOLUMNS (
       Table1[GL Account Number],
       FILTER ( Table1, Table1[Account type] = "Total" )
   )
   

3. Create a column for the original table.
   

   Total_Amount_Column = 
   VAR TypeIndex =
       LOOKUPVALUE ( Table1[Account type], Table1[Index], Table1[Index] - 1 )
   RETURN
       (
           IF (
               Table1[Account type] = "Total",
               CALCULATE (
                   SUM ( Table1[Balance amount] ),
                   FILTER ( ALL ( Table1 ), Table1[Account type] = TypeIndex )
               )
                   + Table1[Balance amount],
               BLANK ()
           )
   )
   

   
4. Drag a slicer and a table into your canvas.
   

For another method you mentioned at last, if the total rows also have a name like Administration costs or something as below. We can create a new column with following formula to fill every normal (GL account type = Account) with this name. (The index column is still required here)

Name_Column = 
IF (
    ISBLANK ( Table1[Name] ),
    LOOKUPVALUE (
        Table1[Name],
        Table1[Index], CALCULATE ( MAX ( Table1[Index] ), ALLEXCEPT ( Table1, Table1[Account type] ) )
            + 1
    ),
    Table1[Name]
)

 Regards,

[Guustc]

@v-sihou-msft thanks for your reply!

I wanted to implement the second solution, but I can not get it to work. I made an index colum and used the formula you gave me, but it does exactly the opposite. I tried to change the if_false value to if_true, but then i get an DAX error.

You can see the example above.

I tried many things, but i do not know what i am doing from. Do you have an idea?

[v-sihou-msft · Accepted solution]

@Guustc

According to your example, we need to update the formula of Kostensoort column as below.

Kostensoort =
VAR Name_Index =
    IF (
        ISBLANK ( Grootboekrekening[Naam] ),
        CALCULATE (
            MIN ( Grootboekrekening[Index] ),
            FILTER (
                Grootboekrekening,
                Grootboekrekening[Index] > EARLIER ( Grootboekrekening[Index] )
                    && Grootboekrekening[Naam] <> BLANK ()
            )
        ),
        Grootboekrekening[Index]
    )
RETURN
    (
        LOOKUPVALUE ( Grootboekrekening[Naam], Grootboekrekening[Index], Name_Index )
)

Regards,

[Guustc]

@v-sihou-msft that works fine! Enormous thanks! Almost got a headache out of this problem, this really helps me go forward 🙂

[Guustc]

Anyone who can help?

Maybe ideas to transform the data?