Forum Discussion

Guustc's avatar
Guustc
Icon for Helper I rankHelper 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,

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.