March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 PowerBIDesktop,
Any Ideas would be appreciated.
Solved! Go to Solution.
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])) )
Hi @sandeepk66
Just checking. In the first line of each of your product groupings, you use three column to determine the result, then from that result, the next lines only use 2 columns. Is that what you meant to do, or was that a typo?
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])) )
Appreciated for your work, its working for that example I Provided.
However, its not working for this scenario. Please find the EXCEL and pbix.
Thank you!
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])) )
Hi @Phil_Seamark,
Appreciated your inputs.
However,Its not working for the same NAME for that day.
Link to pbix
Please see the Issue that I ran into:
Issue example 1
Issue example 2
Can we do this Cumulative Sum by Dimension in Power Query?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
147 | |
92 | |
70 | |
58 |