Forum Discussion

Advocate I

8 years ago

Solved

Need Cumulative Sum by Item

Hi Geeks, Need Cumulative Sum of the AMOUNTper each Item. I have created a column ' Cumulative Sum' in Excel, which is basically AMOUNT+Qty-Liability per Item. needed to replicate same in PowerBI...

Need Help

Phil_Seamark

8 years ago

Hi Again sandeepk66

In anycase, here is a calculated column that might work

Column = 
VAR StartRowAmount = MINX(FILTER(Examples,'Examples'[ITEM] = EARLIER('Examples'[ITEM])),'Examples'[AMOUNT])
RETURN 
    CALCULATE(
                        StartRowAmount + 
                        SUM([SALE Amount]) - 
                        SUM([Liability])
                        , FILTER(
                        ALL('Examples'),
                        'Examples'[RowID] <= EARLIER('Examples'[RowID]) 
                        && 'Examples'[ITEM] = EARLIER('Examples'[ITEM]))
                        )

Phil_Seamark

Microsoft Employee

8 years ago

HI sandeepk66

Try adding this code as a calculated column to your Query1 table, rather than to your Examples table.

Also, I note the data in your INVENTTRANSID column is not in order. Is that important?

CUMColumn2 = 
VAR StartRowAmount = MINX(
                        FILTER(Query1,'Query1'[NAME] =EARLIER('Query1'[NAME])),'Query1'[PHYSICALINVENT])
RETURN 
    CALCULATE(
                        StartRowAmount + 
                        SUM(Query1[RECEIPTQTY]) - 
                       sum(Query1[ISSUEQTY])
                        , FILTER(
                        ALL('Query1'),
                        'Query1'[INVENTTRANSID] <= EARLIER('Query1'[INVENTTRANSID])
                        && 'Query1'[NAME] = EARLIER('Query1'[NAME]))
                        )