Forum Discussion

Guustc's avatar
Guustc
Helper I
10 years ago
Solved

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.

Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    10 years ago

    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,

9 Replies

  • Guustc's avatar
    Guustc
    Helper I
    10 years ago

    Anyone who can help?

    Maybe ideas to transform the data?

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    10 years ago

    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's avatar
      Guustc
      Helper I
      10 years ago

      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's avatar
        v-sihou-msft
        Microsoft Employee
        10 years ago

        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,

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      oajayi
      Frequent Visitor
      8 years ago

      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.

    • oajayi's avatar
      oajayi
      Frequent Visitor
      8 years ago

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

      Thanks.