cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Patron

## Running Sum based on rank

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.

2 ACCEPTED SOLUTIONS
Super User

Hi,

Try this measure

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

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.

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.

10 REPLIES 10
Post Patron

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])

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

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.

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.

Super User

@kumsha1 , Try like

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

Post Patron

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.

Super User

Hi,

Try this measure

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Post Patron

Hi @Ashish_Mathur , measure almost worked, thanks !

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

Super User

Hi,

Try this revised measure

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Post Patron

@Ashish_Mathur , i have below values now.

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

Super User

Hi,

Try this measure

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

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.