Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sandeepk66
Advocate I
Advocate I

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 PowerBIDesktop,

Any Ideas would be appreciated.

 

Link to Dashboard pbix

Excel Data

 

1 ACCEPTED 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]))
                        )

 

examples.png

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Phil_Seamark
Employee
Employee

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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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]))
                        )

 

examples.png

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark

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!

 

Here is the Data

Here is the pbix file

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]))
                        )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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? 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.