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
ianneg77
Frequent Visitor

Cumulative / Running total for a group of orders

I am attempting to get a running total for a list of open orders by item number. It is something done easily in Excel, but I can’t seem to get it work in PowerBI.

 

It involves the calculation in Col. AA. Which is the cumulative count based on the Excel formula below:

**bleep**: =IF(H2=H1,J2+AA1,J2)

 

#1.png

 

Because PowerBI doesn’t due multi-levels sorts, I made sure the base data is sorted in Excel before coming into PowerBI. Data is sorted by Item number then Request Date. I added an index column so lines get a value in sequential order to my desired sort.

I wrote a PowerBI formula that is supposed to mimic the Excel formula above.

 

CUMULATIVE = IF(Open_Orders[item_no]=Open_Orders[PreviousITEM],Open_Orders[QTY ORDERED]+Open_Orders[PreviousQTY],Open_Orders[QTY ORDERED])

 

To get the “PreviousITEM” in the red **bleep** formula line above I created this:

PreviousITEM = CALCULATE(MAX(Open_Orders[item_no]),FILTER(Open_Orders,Open_Orders[Index]=EARLIER(Open_Orders[Index])-1))

 

To get the “PreviousQty” in the red **bleep** formula above I created this:

PreviousQTY = CALCULATE(MAX(Open_Orders[QTY ORDERED]),FILTER(Open_Orders,Open_Orders[Index]=EARLIER(Open_Orders[Index])-1))

 

BUT THE PROBLEM IS WHEN I HAVE MORE THAN 2 MATHCHING ITEM #’s IN A ROW. THE CUMULATIVE CALC IS NOT CORRECT. I KNOW MY CUMULATIVE IF STATEMENT IS MISSING SOMETHING TO ADD A RUNNING TOTAL OF NOT JUST 1 LINE ABOVE, BUT ALL THE ROWS ABOVE WITH MATCHING ITEMS #’s.

#2.PNG

 

9 REPLIES 9
ianneg77
Frequent Visitor

Unfortunately, it didn;t solve the issue. Can t seem to get it to work.

ianneg77
Frequent Visitor

I see that your suggestion was a measure and not a column. Thank you for that...

 

I should clarify, I am looking for a running total for the unique grouping of identical item numbers, not the entire list.

 

My base data is sorted by item # then Requested date, so if i have a group of 8 identical items numbers in sequence, each line's "QTY ORDERED" is being added to the cumulative QTY ORDERED from the identical item number's line above. On the 9th row if the item # changes the count resets.  

 

Example of the pattern:

 

bettercalc.PNG

 

 

 

 

 

 

 

Hi @ianneg77 ,

 

Have you solved your problem?

 

If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If you still need help, plese feel free to ask.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @ianneg77 ,

 

For your requirement, I'm afraid that you should create a group index with dax then use the measure I provided before.

 

Please refer to this similar thread.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @ianneg77 ,

 

If you have an index column, you could try the measure below.

 

Measure =
CALCULATE (
    SUM ( 'Table1'[Qty ordered] ),
    FILTER ( ALL ( 'Table1' ), 'Table1'[Index] <= MAX ( 'Table1'[Index] ) )
)

Here is the output.

Capture.PNG

Hope this can help you!

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Unfortunately, that didn't seem to fix the problem. When I used the suggested column formula and inserted it into the matrix, I got the same value for all lines.

 

New Calc.PNG

 

NEW **bleep** CALC = CALCULATE (
SUM ( 'Open_Orders'[QTY ORDERED] ),
FILTER ( ALL ( 'Open_Orders' ), 'Open_Orders'[Index] <= MAX ( 'Open_Orders'[Index] ) )
)

 

 

Hi @ianneg77 ,

 

If it is convenient, could you share a dummy pbix file which can reproduce the scenario, so that I can have a test on it? You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.)

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello,

Index rows 38 - 46 are the perfect example of what i am attempting to do. Cumulative amount should be getting bigger incrementally since the item numbers match.  My cumulative calc partially works, it will get it correct the for the first 2 instances but any time the matching items numbers go beyond that it stops.

 

Here is a link to the file, https://www.dropbox.com/s/yhxf7k03reqxp2a/Backlog%20Report%20-%20Daily%20DUMMY%20COPY.pbix?dl=0 

 

It will expire tomorrow.

Hi @ianneg77 ,

 

Based on your sample pbix, it seems that you created a calculated column, you should create a measure with the formula in my first reply.

 

measure.png

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.