Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |