cancel
Showing results for
Did you mean:
Highlighted
Helper V

## 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

Accepted Solutions
Highlighted
Super User III

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
Highlighted
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
Highlighted
Super User III

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
Highlighted
Super User IV

@kumsha1 , Try like

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

Proud to be a Super User!

Highlighted
Helper V

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.

Highlighted
Helper V

Hi @Ashish_Mathur , measure almost worked, thanks !

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

Highlighted
Super User III

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
Highlighted
Helper V

@Ashish_Mathur , i have below values now.

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

Highlighted
Super User III

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
Highlighted
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.

Highlighted
Helper V

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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors