Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Cumulative Total

I have tried many functions for running total, but it isn't still the correct result. I would like to create the running total by Hour. For example, if a shift takes a 10 hour workday, I would like to know the accumulation of the number of products  since 1 hour to 10 hours. 

Could you please help me how to get the result?

 

Thank you in advance.

 

Need Help
Show and Tell
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    I am not sure how your semantic model looks like, but please try something like below whether it suits your requirement.

     

    expected result cumulative measure: =
CALCULATE (
    SUM ( 'nexeeddata'[countagghr] ),
    FILTER (
        ALL ( 'nexeeddata' ),
        'nexeeddata'[Hour] <= MAX ( 'nexeeddata'[Hour] )
    ),
    VALUES ( 'nexeeddata'[Shift] ),
    VALUES ( 'nexeeddata'[shiftsw] )
)
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    I made simple samples and you can check the results below:

    Total = var _t = ADDCOLUMNS('Table',"Total",SUMX(FILTER(ALL('Table'),[Hour]<=EARLIER([Hour])),[Value]))
RETURN SUMX(_t,[Total])

     

    An attachment for your reference. Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

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

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    Hi,

    I am not sure how your semantic model looks like, but please try something like below whether it suits your requirement.

     

    expected result cumulative measure: =
CALCULATE (
    SUM ( 'nexeeddata'[countagghr] ),
    FILTER (
        ALL ( 'nexeeddata' ),
        'nexeeddata'[Hour] <= MAX ( 'nexeeddata'[Hour] )
    ),
    VALUES ( 'nexeeddata'[Shift] ),
    VALUES ( 'nexeeddata'[shiftsw] )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    I made simple samples and you can check the results below:

    Total = var _t = ADDCOLUMNS('Table',"Total",SUMX(FILTER(ALL('Table'),[Hour]<=EARLIER([Hour])),[Value]))
RETURN SUMX(_t,[Total])

     

    An attachment for your reference. Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

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