Forum Discussion

Julia2023's avatar
Julia2023
Icon for Helper I rankHelper I
1 year ago

Cumulative sum for ranking

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
"DAX " "DAX help" 'Need help'

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      Julia2023
      Icon for Helper I rankHelper I
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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's avatar
    rohit1991
    Icon for Super User rankSuper User
    1 year ago

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