cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors