DAX Running Total based on Index

[Anonymous]

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 “-“.

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!

[amitchandak]

@Anonymous , I think you need sub category index

https://www.youtube.com/watch?v=7ypdbuCeBtk

https://www.youtube.com/watch?v=7CqXdSEN2k4

[Anonymous]

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. 

[ChenwuZhu_Gmail · 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:

New column to calculate:

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

Result:

Measure:

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

Result:

Best regards.

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

[Anonymous]

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