Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

 

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!

Need Help
  • ChenwuZhu_Gmail's avatar
    ChenwuZhu_Gmail
    4 years ago

    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.

     

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        ChenwuZhu_Gmail
        Icon for Resolver I rankResolver I
        4 years ago

        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.