Forum Discussion
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 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.
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
12 Replies
This is a standard pattern. You can read about it here https://www.daxpatterns.com/abc-classification-dynamic/
Anonymous ,
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
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
