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

Post Patron

## Semi cumulative sum of column values in a table

Hello, I have a table visualization that I generated in power BI and would like to generate another tables that is laid out just like the first table but with semi-cumulative sum of values. I have attached this photo to illustrate what I mean:

The first table(columns A-F) was created using the Matrix visualization; and I also want to create the second table (columns H-M)  using the matrix visualization but I want the values from 2020 year and onward to be sum of previsous year and current year as I have outlined in the picture above. I was wondering, is there a way to do that? If someone could please help, that would be much appreciated. In excel it can be easily done, but how can I replicate that in Power BI?

This is how my raw data table looks like:

2 ACCEPTED SOLUTIONS
Super User

1. i don't know why it does not work for your real data. Maybe you need to update your sample data.

2. if you want to get the correct total value,you can create a measure

``Measure 2 = sumx(VALUES('Table 2'[month]),[Measure])``

3. you just need to create an order column in date table.

Proud to be a Super User!

Post Patron

@ryan_mayu but the summation is wrong though, I don't know who accepted this as the solution but if you look at April 2020 value, it should be 2000 not 10000. For some reason, the totals are off and that is what I am struggling with. Is there a way to fix that?

11 REPLIES 11
Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

here is a workaround for you

create a date table and create a measure

``````Measure =
if(min('Table 2'[Date])>=today(),blank(),Maxx(FILTER(all('Table'),month(max('Table 2'[Date]))=month('Table'[Custom])),'Table'[value])*(max('Table 2'[year])-2018))``````

pls see the attachment below

Proud to be a Super User!

Post Patron

@ryan_mayu  thank you, I dont think it worked for me. This is what I get:

Also, is there a reason why the column totals are not adding up properly?

EDIT: Sorry, I believe I know what the problem is, I failed to mention that the month order for my visualization is different since we count fiscal year differently, which creates lots of issues. I created a table in my power bi to illustrate how we order months:

Apologies in advance, if you could help solve the issue, I would much appreciate

Super User

1. i don't know why it does not work for your real data. Maybe you need to update your sample data.

2. if you want to get the correct total value,you can create a measure

``Measure 2 = sumx(VALUES('Table 2'[month]),[Measure])``

3. you just need to create an order column in date table.

Proud to be a Super User!

Post Patron

@ryan_mayu but the summation is wrong though, I don't know who accepted this as the solution but if you look at April 2020 value, it should be 2000 not 10000. For some reason, the totals are off and that is what I am struggling with. Is there a way to fix that?

Super User

the april 2020 is 2000, pls see the screenshot above, first table was the solution at the first time. The second table was the solution after improvement

Proud to be a Super User!

Post Patron
@ryan_maru I am looking at the screenshot and I don't really see that APR 2020 is 2000, it appears that Apr 2020 is showing up as 10,000 unless its the wrong one? I am looking at your reply from ‎06-24-2023 05:34 PM and from the second image, the totals are not adding up as well. Please let me know your thoughts! Thank you
Super User

pls see the reply on ‎06-25-2023 08:34 AM

create a measure

``Measure 2 = sumx(VALUES('Table 2'[month]),[Measure])``

Proud to be a Super User!

Post Patron

@ryan_mayu
I don't think the values add up horizontally, they only add up vertically

Super User

pls see the attachment below

Proud to be a Super User!

Post Patron

@ryan_mayu
i tried your way but it is not working for me. I am trying to share my power bi file with you so you can see it but it seems that I don't have an option to share my file with you like you did.
I am sharing images of what my data looks like so you cna get a better idea

The top matrix table was created using your Measure 2 and as you can see, the numbers are incorrect: e.g. for May 2024-2025 the value should be \$5265,208+\$4282,124=\$9547332. But your measure gives me a different value and the month orders are incorrect

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.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors