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
CustomerProductmonthly salesSales%Comulative%
CustAProd123622%22%
CustAProd218517%39%
CustAProd318117%55%
CustAProd414213%68%
CustAProd513612%81%
CustAProd611511%91%
CustAProd7959%100%
CustBProd19219%19%
CustBProd28618%36%
CustBProd37916%53%
CustBProd47916%69%
CustBProd57816%85%
CustBProd67515%100%

 

I cant share teh data model. the data is sensitive. But this is more or less what i have. I want to calculate the running total for all product for each customer sorted from high to low

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    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! 🤠