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

  • 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! 🤠