Running Sum based on rank

[kumsha1]

Hi,

Can someone help me with calc. for RunningSum (Duration) based on the Rank. Below is the table data. TIA, cheers.

Rank and Duration are calc. measures.

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

=SUMX(TOPN([Rank],CALCULATETABLE(VALUES(Data[Name]),ALL(Data[Name])),[Rank],1),[Duration (mins) - ALL])

[kumsha1]

Both measures worked for my requirement, thanks @Ashish_Mathur and @v-zhenbw-msft 

=SUMX(TOPN([Rank],CALCULATETABLE(VALUES(Data[Name]),ALL(Data[Name])),[Rank],1),[Duration (mins) - ALL])

[Ashish_Mathur]

You are welcome.

[v-zhenbw-msft · Accepted solution]

Hi @kumsha1 ,

Please try the measure like following screenshot.

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[amitchandak]

@kumsha1 , Try like

calculate(sum(table[duration]), filter(table, table[duration]<=max(table[duration])))

[kumsha1]

Thanks @amitchandak for quick reply.

Tried the formula in cal. column/measure has returned same values as Duration. 

calculate(sum(table[duration]), filter(table, table[Rank]<=max(table[Rank]))) in a cal.column returned circular dependency error.

[Ashish_Mathur]

Hi,

Try this measure

=SUMX(TOPN([Rank],CALCULATETABLE(VALUES(Data[Name]),ALL(Data[Name])),[Duration (mins) - ALL],1),[Duration (mins) - ALL])

[kumsha1]

Hi @Ashish_Mathur , measure almost worked, thanks !

how do i get the values in correct direction. Sorting on Rank didn't help.

[Ashish_Mathur]

Hi,

Try this revised measure

=SUMX(TOPN([Rank],CALCULATETABLE(VALUES(Data[Name]),ALL(Data[Name])),[Duration (mins) - ALL]),[Duration (mins) - ALL])

If it does not work, then share the link from where i can download your PBI file.

[kumsha1]

@Ashish_Mathur , i have below values now.

Sorry mate, .pbix can't be shared due to security reasons, it has all other Org stuff.

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

=SUMX(TOPN([Rank],CALCULATETABLE(VALUES(Data[Name]),ALL(Data[Name])),[Rank],1),[Duration (mins) - ALL])