cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NielsDW
Frequent Visitor

Cumulative sum of measure (DAX) without index number

Hi all,

I am strugging to calculate a cumulative sum in a table visual. I would like to auto number the rows in the table visual, independent of any sorting. Thus, creating an index column in power query is not an option. After sorting different fields, the dynamic index number needs to be recalculated so it is always count from 1 to N. Since i do not know if this is even possible in Power BI, my rows do not have an index number at the moment (nor a date). 

I only have a unique ID number (string value). The table contains one measure which sums up a specific value. I want to calculate the cumulative sum of this measure starting from the first row in the table visual. I've tried to use a quick measure "running total" but you have to specify an index number or at least a date for this I suppose. 

It is quite an easy requirement but it seems so hard to realize in Power BI. Can someone help me with this please? Pbix file cannot be shared unfortunately, would appreciate some help a lot!
Kind regards

5 REPLIES 5
AlexisOlson
Community Champion
Community Champion

So you want an index column in your visual that doesn't change regardless of what sorting you apply to the visual? I'm not sure this is possible.

 

Creating a cumulative sum sounds more feasible. How is your visual currently sorted? In order of the ID number?

Thanks for the fast reply,

I want the index to be recalculated every time i apply a sort filter which means the index starts everytime from the first row of the table.

My table is not sorted at all at the moment. We could sort on the ID number but it doesn't make any sense. The table contains about 8 columns and 5 of them will be used to sort. So default sorting is not really the case.

AlexisOlson
Community Champion
Community Champion

As far as I understand, the interactive sorting of a table is purely visual and can't be detected by a DAX measure. Thus I can't think of any way to make this work like you're describing. I'd love to be proven wrong though.

And there is no way I can calculate a cumulative sum of a measure based on each previous row? I do need a static index number to calculate a running total?

I would like to calculate the cumulative sum based on the returned rows in my table, no matter what sorting is applied ...


AlexisOlson
Community Champion
Community Champion

DAX doesn't really have a concept of row number. Everything is based on filters. You can write cumulative totals for specific sorting but not for whatever ordering is shown in a visual.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors