Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Guustc
Helper I
Helper I

Totalling Ledger NAV

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.

1 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 )
)

 

33.jpg

 

Regards,

View solution in original post

9 REPLIES 9
clausm73
Helper III
Helper III

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

Anonymous
Not applicable

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.

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

Thanks.

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

@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.
    11.jpg
  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 ()
            )
    )
    
    22.jpg
  4. Drag a slicer and a table into your canvas.
    333.jpg

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]
)

6666.jpg

 Regards,

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

 

VB.PNG

 

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?

@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 )
)

 

33.jpg

 

Regards,

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

Guustc
Helper I
Helper I

Anyone who can help?

Maybe ideas to transform the data?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.