Forum Discussion

Power-BI4CPF's avatar
Power-BI4CPF
New Member
2 years ago
Solved

Cumulative sum on a ranked table

I'm trying to extract the top % of products by sales for each customer. the script below returns the cumulative percentage but not in order. What I want is to sort them from high to low and then run ...
Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    Power-BI4CPF 

    output

     

     

    measures : 
    ranking measure : 

    rnk = 
RANKX(
    ALLSELECTED(tbl1[Product]),CALCULATE(sum(tbl1[monthly sales])),,DESC)

     

     

    measure cumul : 

    cumulative sum = 
var current_ranking  = [rnk]


var s = 
CALCULATE(
    SUM(tbl1[monthly sales]),
    FILTER(
        ALLSELECTED(tbl1[Product]),
        [rnk] <=current_ranking
    )

)

return s

     

     

     

    cumult%

    cumult % = 
[cumulative sum] 
/ 
CALCULATE(SUM(tbl1[monthly sales]), ALLSELECTED(tbl1[Product])
)

     

     

     

     

     

    if my ansswer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
    It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

     

Power-BI4CPF's avatar
Power-BI4CPF
New Member
2 years ago

 

This is a list of all the product for a certain customer. The sales column is already sorted from high to low. I want the cumlutavie% column to be the running sum of Sale%. right not it is calculating the running sum but in a different order. In the end I want to be able to filter products by the top % (i.e. top50% or top 80%)