cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors