cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Change Structure of Table to be Cumulative

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?

1 ACCEPTED SOLUTION
Super User

you can modify the DAX

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

Proud to be a Super User!

5 REPLIES 5
Super User

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])``````

Proud to be a Super User!

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?

Super User

you can modify the DAX

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

Proud to be a Super User!

Fantastic - thank you @ryan_mayu!

Super User

you are welcome

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.