Forum Discussion

feralvarez994's avatar
feralvarez994
Icon for Helper II rankHelper II
3 years ago
Solved

Calculate cumulative average delay between several columns

Hey! Im looking forward calculating the average delay per category, i have one column per different delay and want to show them all as acumulative in a line chart or stack area chart. I currently have this chart that shows the average per category, but i want to show it too as acumulative to make the user realize the total estimated delay. How can i do that? I do not have time series here, just categories and delay per category.

 

Im attaching the power bi file for anyone that can helps me, thanks!

  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi feralvarez994 ,

     

    Does the cumulative value you mentioned refer to the cumulative value of the average of each species? If so, please try this:

    Here is my simple sample:

     

    Measure = 
var _a = SUMMARIZE(ALL('Table'),'Table'[ID],"Average",AVERAGE('Table'[Value]),"Rank",RANKX(ALL('Table'),[ID],,ASC,Dense))
var _b = MAXX(FILTER(_a,[ID]=MAX('Table'[ID])),[Rank])
return SUMX(FILTER(_a,[Rank]<=_b),[Average])

    Output:

    Note: If the categories on your x-axis are not in a specific order, you might consider adding columns for sorting it using the switch function

     

    Best Regards,

    Jianbo Li

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

     

1 Reply

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi feralvarez994 ,

     

    Does the cumulative value you mentioned refer to the cumulative value of the average of each species? If so, please try this:

    Here is my simple sample:

     

    Measure = 
var _a = SUMMARIZE(ALL('Table'),'Table'[ID],"Average",AVERAGE('Table'[Value]),"Rank",RANKX(ALL('Table'),[ID],,ASC,Dense))
var _b = MAXX(FILTER(_a,[ID]=MAX('Table'[ID])),[Rank])
return SUMX(FILTER(_a,[Rank]<=_b),[Average])

    Output:

    Note: If the categories on your x-axis are not in a specific order, you might consider adding columns for sorting it using the switch function

     

    Best Regards,

    Jianbo Li

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