Forum Discussion

BEENSGROEPADMIN's avatar
BEENSGROEPADMIN
Frequent Visitor
3 years ago

Cummulative sum by one value till maximum

Hello,

 

The following table is relevant to know for this case.

1. Production quantities: this table lists a quantity of the total project per production activity:

The first row contains production activities, the second contains the units, the third contains the total quantity of the production activity and the last contains the average cycle time of the production activity.

 

Another table lists the produced quantity of an production activity by week. 

I want to make an line-chart with two lines:

1. Is the cummulative sum of the registrated production quantity (this line already consists)
2. A line that sums one value of 'Doorlooptijdbegroting' (average cycle time) till the max of that production activity named 'Hoeveelheden' (total amount).
Example: 

one activity 'Aanbrengen damwanden (drukken)' has a total amount of 227 meters. The average cycle time is 56 meters per week. The line has to cummulate the 56 till it has reached the total amounto 227: 56+56+56+56+rest (3).

 

Note: Both tables are related to a date table with weeknumbers.

 

I am hoping some could help me! Thanks a lot.

 

Kind regards,

 

Admin BG

#cummulative #sum #datetable

 

Need Help

2 Replies

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    3 years ago

    Hi, @Syndicate_Admin

    I'm curious to know how your week chart relates to the data sheet. You can try the following methods.

    Measure1 = 
Var _aver1=CALCULATE(MAX('Table'[Doorlooptijdbegroting]),FILTER(ALL('Table'),[Activiteit]="Aanbrengen damwanden (drukken)"))
Var _max1=CALCULATE(MAX('Table'[Hoeveelheden]),FILTER(ALL('Table'),[Activiteit]="Aanbrengen damwanden (drukken)"))
Var _runningsum=SUMX(FILTER(ALL(Week),Week[Week]<=SELECTEDVALUE(Week[Week])),_aver1)
Return
IF(_runningsum<=_max1,_runningsum,"descanso")
    Measure2 = 
Var _aver1=CALCULATE(MAX('Table'[Doorlooptijdbegroting]),FILTER(ALL('Table'),[Activiteit]="Aanbrengen damwanden (trillen)"))
Var _max1=CALCULATE(MAX('Table'[Hoeveelheden]),FILTER(ALL('Table'),[Activiteit]="Aanbrengen damwanden (trillen)"))
Var _runningsum=SUMX(FILTER(ALL(Week),Week[Week]<=SELECTEDVALUE(Week[Week])),_aver1)
Return
IF(_runningsum<=_max1,_runningsum,"descanso")

    If the result is different from what you expected, please tell more details.

    Best Regards,

    Community Support Team _Charlotte

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

  • BEENSGROEPADMIN's avatar
    BEENSGROEPADMIN
    Frequent Visitor
    3 years ago

    Hello,

     

    I've created the following measure based on your measures:

     

    The following line is generated by this measure:

    This line starts at the first week of the year. Now I want to start this line chart at the same moment when the first production activity is registrated. This date value is " 'table - productregistration'[when] ".

     

    Do you know what I should change in this Measure? Maybe it is relevant to know the following date table:

     

    Thanks a lot for helping me out!