Cumulative sum for ranking

[Julia2023]

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

[Jihwan_Kim]

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 )

[Julia2023]

@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? 

[Anonymous]

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.

[rohit1991]

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