Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculating Running Total Parititioned by Column Value

Hi guys, 

First post on here so if I don't ask with enough detail please let me know. 

 

I have built a cashflow report which relies upon the calculation of a running balance total. 

 

 

The screenshot above shows both my DAX and my problem. 

 

You will note I have filtered the data set because all of the rows in between Opening Balance and Closing Balance aren't relevant, however I can confirm that rows 1 and 17 are working perfectly, my amount for row 1 appears as my running balance for row 1 as it should, being that it's the first entry in the list. 

 

row 17 is also correct, the running balance figure in this case has taken the amount from row 1, totaled up all of the hidden rows between row 1 and 17 and produced the correct closing balance. 

 

Herein lies the problem, row 18 displays the correct amount for the opening balance but the running balance figure also takes into account the rows above it. 

 

We can see that the final column "Loc" shows the paritioning in the data I am looking for (again apologies if partition is the wrong phrase, I am experienced in T-SQL and to achieve what I want here I would sum partitioning on the "LOC" column)

 

In short, I am looking to calculate the running balance per LOC, and effectively only total up the rows into the running balance column where they match the Loc column. 

 

So in the case of row 18 the running balance should be 50,527.31

Row 34 should be the 50,527.31 + whatever lies between row 18 and 34 as they all contain the same "Loc" reference. 

 

Hopefully this makes sense, any help much appreciated. 

 

 

 

  • Anonymous,

     

    Try this calculated column:

     

    Running Balance = 
    VAR vIndex = 'Cashflow Table'[Index]
    VAR vResult =
        CALCULATE (
            SUM ( 'Cashflow Table'[Amount] ),
            ALLEXCEPT (
                'Cashflow Table',
                'Cashflow Table'[loc],
                'Cashflow Table'[periodend]
            ),
            'Cashflow Table'[Index] <= vIndex
        )
    RETURN
        vResult

     

     

1 Reply

  • Anonymous,

     

    Try this calculated column:

     

    Running Balance = 
    VAR vIndex = 'Cashflow Table'[Index]
    VAR vResult =
        CALCULATE (
            SUM ( 'Cashflow Table'[Amount] ),
            ALLEXCEPT (
                'Cashflow Table',
                'Cashflow Table'[loc],
                'Cashflow Table'[periodend]
            ),
            'Cashflow Table'[Index] <= vIndex
        )
    RETURN
        vResult