Semi cumulative sum of column values in a table

[OPS-MLTSD]

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:

[ryan_mayu · Accepted solution]

@OPS-MLTSD 

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. 

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[ryan_mayu]

@OPS-MLTSD 

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

[OPS-MLTSD]

@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 

[ryan_mayu · Accepted solution]

@OPS-MLTSD 

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. 

[OPS-MLTSD · Accepted solution]

@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?

[ryan_mayu]

@OPS-MLTSD 

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

[OPS-MLTSD]

@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

[ryan_mayu]

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

create a measure

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

[OPS-MLTSD]

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

[ryan_mayu]

pls see the attachment below

[OPS-MLTSD]

@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