cumulative sum without date

[alexrf86]

Hi! I need to identify the most sold products which represent 80% of my sales. For instance:

PRODUCT           SALES           CUMULATIVE SALES

A                           50                             50

B                           20                             70

C                          10                              80

D                           8                              88

E                           7                               95 

F                           5                              100

Therefore, products A, B and C represents 80% of my sales.

I am able to get cumulative sales with a date dimension, but for this exercise, date is not taken into account.

Regards.

[amitchandak]

@alexrf86 ,

Can you try like

Cumm Sales = CALCULATE(SUM(Table[Sales]),filter(Table,Table[PRODUCT] <=maxx(Table,Table[PRODUCT])))
Cumm Sales = CALCULATE(SUM(Table[Sales]),filter(Table,Table[PRODUCT] <=max(Table[PRODUCT])))

You can also use the quick measure to create running total

[alexrf86]

Thanks for your answer but none of your measures cumulate sales. All of them only show up Sales without cumulation

[amitchandak]

Try with all. Have you tried quick measure

Cumm Sales = CALCULATE(SUM(Table[Sales]),filter(all(Table),Table[PRODUCT] <=maxx(Table,Table[PRODUCT])))
Cumm Sales = CALCULATE(SUM(Table[Sales]),filter(all(Table),Table[PRODUCT] <=max(Table[PRODUCT])))

https://docs.microsoft.com/en-us/power-bi/desktop-quick-measures

[alexrf86]

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]

@alexrf86 , 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])))

[alexrf86]

@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.

[amitchandak]

@alexrf86 , Please find the attached solution

[alexrf86]

@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

[amitchandak · Accepted solution]

@alexrf86 

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

[alexrf86]

@amitchandak This is the measure I was looking for!

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

Thanks a million! 

[MattAllington]

This is a standard pattern. You can read about it here https://www.daxpatterns.com/abc-classification-dynamic/

[alexrf86]

Sorry but this example does not fit as my exercise has only one table with these two columns. I just need to set up cumulative sales