Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
KatieH
Advocate IV
Advocate IV

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 NameQuarterSoldWaste
Group ABCQ1102
Group ABCQ2153
Group ABCQ351
Group ABCQ48

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 NameQuarterSoldWaste
Group ABCQ1102
Group ABCQ2255
Group ABCQ3306
Group ABCQ438

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

@KatieH 

you can modify the DAX

 

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
ryan_mayu
Super User
Super User

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

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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?

@KatieH 

you can modify the DAX

 

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Fantastic - thank you @ryan_mayu!

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors