Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Running Total not correct when adding extra field

Hi!

 

I have been using this running total measure for several years now and never had any problem with the result, until now.

 

CALCULATE(SUM(fact[Quantity]),FILTER(ALLSELECTED(calendar[datetime]),calendar[datetime]<= MAX(calendar[datetime])))

As you can see everything works fine in the screenshot below.
 

 

But when I add the field 'Ordernumber' from the same table 'fact' the running total (cumulative) is not working anymore. I really do not understand why? I hope some of you do;) please enlighten me!



 

help needed
running total

9 Replies

  • Tanushree_Kapse's avatar
    Tanushree_Kapse
    Impactful Individual
    5 years ago

    Anonymous ,
     Try this measure:

    RunningTotal =
    CALCULATE (
     SUM ( fact[Quantity] ),
     FILTER ( ALL ( fact ), calendar[Datetime] <= MAX ( calendar[Datetime] ) );
     VALUES ( fact[ordernumber )
    )

     

     

    I hope this helps!

    Mark this as a solution if it answers your queation. Kudos are always appreciated.

    Thanks! 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Tanushree_Kapse 

       

      The measure not working, because the ALL includes the Fact and therefore I cannot filter the calendar. And could you maybe explain why I need tho add the Values part. I never had to use it before to get the correct outcome.

       

      RunningTotal =
      CALCULATE (
       SUM ( fact[Quantity] ),
       FILTER ( ALL ( fact ), calendar[Datetime] <= MAX ( calendar[Datetime] ) );
       VALUES ( fact[ordernumber )
      )

       

      • Tanushree_Kapse's avatar
        Tanushree_Kapse
        Impactful Individual
        5 years ago

        Hi Anonymous ,

         

        Sorry for the typo.

         

        Try this:

        RunningTotal =
        CALCULATE (
         SUM ( fact[Quantity] ),
         FILTER ( ALL ( calendar), calendar[Datetime] <= MAX ( calendar[Datetime] ) );
         VALUES ( fact[ordernumber )
        )


        Also here, the VALUES() puts the Ordernumber into the filter context of the CALCULATE()-statement. Otherwise you would have running totals on the dates, but it would be the same for all your Ordernumbers. 

         

        Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi amitchandak 

    This first measure with ALL does work, but I want to be able to filter on a Ordernumber and then it does not show the correct amount. The second measure with ALLSELECTED is almost correct!

     

    The only thing is that the amount in yellow are correct, but only for the last row, very strange.