Change Structure of Table to be Cumulative

[KatieH]

Hi all,

Looking for a bit of help on how to achieve this. I have a data table of clients with two separate amounts showing sales and waste per quarter as shown here:

Client Name	Quarter	Sold	Waste
Group ABC	Q1	10	2
Group ABC	Q2	15	3
Group ABC	Q3	5	1
Group ABC	Q4	8	4

I would like to amend the table so that it shows the cumlative number for each quarter (so Q2 = Q2 + Q1, Q3 = Q3 + Q2 + Q1 etc.) like this:

Client Name	Quarter	Sold	Waste
Group ABC	Q1	10	2
Group ABC	Q2	25	5
Group ABC	Q3	30	6
Group ABC	Q4	38	10

Ideally I would like this to be done in the dataset itself rather than using measures etc. due to the way the rest of the report is formatted. Can anyone advise what steps I'd need to take to achieve this?

[ryan_mayu]

@KatieH 

is this what you want?

sold2 = sumx(FILTER('Table','Table'[Quarter]<=EARLIER('Table'[Quarter])),'Table'[Sold])

waste2 = sumx(FILTER('Table','Table'[Quarter]<=EARLIER('Table'[Quarter])),'Table'[Waste])

[KatieH]

Hi @ryan_mayu 

That's almost what I want but that is summing everything in the table and there are numerous different groups. Is there a way I can make it sum by each particular group?

[ryan_mayu · Accepted solution]

@KatieH 

you can modify the DAX

sold2 = sumx(FILTER('Table','Table'[Client Name]=earlier('Table'[Client Name])&&'Table'[Quarter]<=EARLIER('Table'[Quarter])),'Table'[Sold])

[KatieH]

Fantastic - thank you @ryan_mayu!

[ryan_mayu]

you are welcome