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,

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 create a measure that sums up the Amount field of 5 selected accounts in on the G/L Entry table?

Thanks.