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

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,

    • Guustc's avatar
      Guustc
      Helper I
      10 years ago

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