Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

cumulative sum without date

Hi! I need to identify the most sold products which represent 80% of my sales. For instance:   PRODUCT           SALES           CUMULATIVE SALES A                           50                    ...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous 

    Cumulative is based on the sort order , You can sort on sales

    Cumm1 = CALCULATE(sum('product'[SALES]),FILTER(all('product'),'product'[SALES]>=MAX('product'[SALES])))

     

    Better you create a dense rank and use that. New column

    rank = ranks(all(Product),Product[sales],,asc,dense)

     

    Use rank in last formula

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Sorry but none works:

 

Cumm Sales maxx = CALCULATE(SUM(SALES[SALES]);filter(SALES;SALES[PRODUCT]<=maxx(SALES;SALES[PRODUCT])))
 
Cumm Sales max = CALCULATE(SUM(SALES[SALES]);filter(SALES;SALES[PRODUCT]<=max(SALES[PRODUCT])))
 
amitchandak's avatar
amitchandak
Super User
6 years ago

Anonymous , I think the last column is working, sort on product and check . Also you can move product in separate Tale and join on the product and try

Cumm Sales max = CALCULATE(SUM(SALES[SALES]);filter(All(Product;Product[PRODUCT]<=max(Product[PRODUCT])))

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    amitchandak Thanks for your answer but I need to identify the most sold products, so I need to start cumulating from the most sold to the least sold product.

     

    Creating a new table for Product gives me the same result.

     

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        amitchandak I really appreciate your help but your measure works because A is the most sold product and F is the least sold product.

         

        If you change the product names to other which do not meet the alphabet order, the formula does not work. For instance:

         

         

        May you please try with these product names?

         

        Thanks in advance