topic Cumulative Sum BY CLIENT and without date or index column in DAX Commands and Tips

Cumulative Sum BY CLIENT and without date or index column

TRIXTERBINOOB — Fri, 03 May 2024 22:40:09 GMT

Hello, dear comunity:

I'm here cause need help with doing a cumulative sum of sales wich I calculated it with SUM(), need to be representated on a pivot table in excel or matrix in Power BI with DAX. If I do a sum of sales and bring customername to labels I have this result, and I need a solution to receive the orange column's behavior. the sales must be ordered by "Total sales in period" value.

I got the sample data in this url to practice: Sample Sales Data (kaggle.com)

Sorry for my bad grammar in english, I'm still learning.

Kind regards.

Re: Cumulative Sum BY CLIENT and without date or index column

DataNinja777 — Sat, 04 May 2024 06:03:22 GMT

Hi TRIXTERBINOOB ,

You can achive your required output in many different ways and the following is an example.

First create a ranking of sales by customers.

Then, create a cumulative total in the order of customer sales ranking by value.

I attach an example pbix file below:

Re: Cumulative Sum BY CLIENT and without date or index column

AntrikshSharma — Sat, 04 May 2024 10:24:30 GMT

TRIXTERBINOOB

Measure = CALCULATE ( [Sales], WINDOW ( 1, ABS, 0, REL, ALL ( 'Table'[Etiquetas de fila] ), ORDERBY ( [Sales], DESC ) ) )

Measure 2 = VAR CustomerSales = ADDCOLUMNS ( ALLSELECTED ( 'Table'[Etiquetas de fila] ), "@Sales", [Sales] ) VAR Result = SUMX ( WINDOW ( 1, ABS, 0, REL, CustomerSales, ORDERBY ( [@Sales], DESC ) ), [@Sales] ) RETURN Result

Re: Cumulative Sum BY CLIENT and without date or index column

TRIXTERBINOOB — Sun, 05 May 2024 03:49:34 GMT

It actually worked!

I didn't knew about WINDOW function I investigated documentation and learned a lot from you.

Thank u very much!