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 the code below.

 
CumulativePercentage =
VAR _TotalSales =
    CALCULATE(
        [Sales_kgs],
        ALL('Product Lookup')
    )
VAR _CumSales =
        CALCULATE(
            SUM(Inventory[monthly_wholesale_kg]),
            FILTER(
                ALLSELECTED(Inventory),
                Inventory[product_sku] <= MAX(Inventory[product_sku])
            )
)

RETURN
    _CumSales / _TotalSales
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! 🤠

     

4 Replies

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