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
Anonymous
Not applicable

DAX Running Total based on Index

Hello, I’m new to DAX and I’m trying to get a Running Total (Supply and Demand) based on the column SKU Index:

What I’m trying to do is to replicate the next formula:

  • =IF(N2=1, J2, I1+J2-K2)
    • The 1 in SKU Index indicates whenever an SKU changes and then the Running Total should start again.
    • Supply and Demand is fed from both columns “+” and “-“.

emilianoalvz_0-1659690586826.png

 

I'm doing this inside Excel, first I created that table using Power Query, and now, the last step is to create that Measure with DAX but I don't know how. 

The table with the real data is much larger, it comes from SAP and it has 450K registers. I already uploaded that table to the data model and then 2 pivot tables will be created from that. 

 

The only missing thing is this running total measure based on the SKU Index, any help would be greatly appreciated, thanks in advance!

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

Did you solve your problem, if yes, please mark it as a solution, if not, please try these codes below to do that.

 

My table looks like:

ChenwuZhu_Gmail_0-1660035681587.png

 

New column to calculate:

Column =
SUMX (
    FILTER (
        'Table',
        [SKU] = EARLIER ( 'Table'[SKU] )
            && [SKU Index] <= EARLIER ( 'Table'[SKU Index] )
    ),
    [+] - [-]
)

 

Result:

ChenwuZhu_Gmail_1-1660035747734.png

 

Measure:

Measure =
SUMX (
    FILTER (
        ALLSELECTED ( 'Table' ),
        [SKU] = SELECTEDVALUE ( 'Table'[SKU] )
            && [SKU Index] <= SELECTEDVALUE ( 'Table'[SKU Index] )
    ),
    [+] - [-]
)

 

Result:

ChenwuZhu_Gmail_2-1660035817489.png

 

Best regards.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

Anonymous
Not applicable

Hi! Yes, sorry I forgot to mention that, but it is the last column called SKU index.  

I actually used the first video you suggested from Goodly to create it, and now with that sub category index, I think it is feasible to perform a running total based on that column. 

Hi @Anonymous ,

 

Did you solve your problem, if yes, please mark it as a solution, if not, please try these codes below to do that.

 

My table looks like:

ChenwuZhu_Gmail_0-1660035681587.png

 

New column to calculate:

Column =
SUMX (
    FILTER (
        'Table',
        [SKU] = EARLIER ( 'Table'[SKU] )
            && [SKU Index] <= EARLIER ( 'Table'[SKU Index] )
    ),
    [+] - [-]
)

 

Result:

ChenwuZhu_Gmail_1-1660035747734.png

 

Measure:

Measure =
SUMX (
    FILTER (
        ALLSELECTED ( 'Table' ),
        [SKU] = SELECTEDVALUE ( 'Table'[SKU] )
            && [SKU Index] <= SELECTEDVALUE ( 'Table'[SKU Index] )
    ),
    [+] - [-]
)

 

Result:

ChenwuZhu_Gmail_2-1660035817489.png

 

Best regards.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Anonymous
Not applicable

This is indeed really helpful and works exactly as expected, thanks a lot

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.