topic Cumulative sum for ranking in DAX Commands and Tips

Cumulative sum for ranking

Julia2023 — Fri, 08 Nov 2024 13:15:19 GMT

Hello,

Could someone help me create a Ranking measure that’s a cumulative sum of the Position measure, ensuring it adds up to 100%? Any tips or methods would be really appreciated.

Position = VAR TotalRev = CALCULATE( [revenue], REMOVEFILTERS(ID[Product ID]) ) VAR Result = DIVIDE([revenue], TotalRev, 0) RETURN Result

Re: Cumulative sum for ranking

rohit1991 — Wed, 30 Jul 2025 12:57:58 GMT

Hi Julia2023

To create a cumulative ranking measure that sums up to 100%, try this DAX:

CumulativePosition = VAR CurrentProduct = MAX(ID[Product ID]) RETURN CALCULATE( SUMX( FILTER( ALL(ID[Product ID]), ID[Product ID] <= CurrentProduct),[Position]))

Re: Cumulative sum for ranking

Jihwan_Kim — Sun, 10 Nov 2024 12:25:14 GMT

Hi,

I am not sure how your semantic model looks like but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

revenue: = SUM(sales[revenue])

WINDOW function (DAX) - DAX | Microsoft Learn

percentage by all revenue: = VAR _allrevenue = CALCULATE ( [revenue:], REMOVEFILTERS ( 'ID'[Product ID] ) ) VAR _cumulaterevenue = CALCULATE ( [revenue:], WINDOW ( 1, ABS, 0, REL, ALL ( 'ID'[Product ID] ), ORDERBY ( [revenue:], DESC ) ) ) RETURN DIVIDE ( _cumulaterevenue, _allrevenue )

Re: Cumulative sum for ranking

Julia2023 — Tue, 12 Nov 2024 14:19:16 GMT

Jihwan_Kim, thank you so much for this detailed walkthrough. This solution (WINDOW) was exactly what I was looking for. However, in my case, if I don’t apply the TOP N filter with this measure, I get an error saying there isn’t enough memory. Do you know the reason for this and how I can resolve it?

Re: Cumulative sum for ranking

Anonymous — Tue, 26 Nov 2024 08:35:31 GMT

Hi Julia2023 , hello Jihwan_Kim and rohit1991, thank you for your prompr reply!

Consider optimizing your solution at different architectural layers. Layers include:

The data source(s)
The data model
Visualizations, including dashboards, Power BI reports, and Power BI paginated reports
The environment, including capacities, data gateways, and the network

More information for your reference:

Optimization guide for Power BI - Power BI | Microsoft Learn

Best regards,

Joyce

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