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
Stuznet
Helper V
Helper V

Cumulative Sum of 2 Rows

Hi guys,

 

Capture.PNG

 

Capture2.PNG

 

Above is the formula I created in Excel. Row 4 is what I'm trying to create in Power BI, but I'm not sure how do I achieve that. 

Here is the Dax function I wrote but I'm not getting the correct value in November and December

 

Measure = CALCULATE([Total]),FILTER(ALLSELECTED(Table1),Table1[Month Num] <= Max(Table1[Month Num])))

Can someone please help me?

 

Thank you

1 ACCEPTED SOLUTION

Hi @Stuznet

 

Row3 = 
VAR Row1Sum = CALCULATE(DIVIDE([Total];2);FILTER(ALLSELECTED(Data);Data[Month Num] <= MAX(Data[Month Num]) && Data[Month Num] +1 >= MONTH(TODAY())))
VAR Row2Sum = CALCULATE(DIVIDE([Total];2);FILTER(ALLSELECTED(Data);Data[Month Num] <= MAX(Data[Month Num]) && Data[Month Num] +2 <= MONTH(TODAY()) && Data[ROW] <> BLANK()))
RETURN
Row1Sum + Row2Sum
Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

 

What is actually there in the first column.  Are they years by any chance?  Can you share your PBI file with dummy figures?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur

 

Here is the dummy report Cumulative Sum of 2 Rows, it is exactly what I'm trying to achieve on my active report.

 

Row 3 accumulating from Row 2. IF November and December <> BLANK(), I want it to accumulate from Row1 starting from October to December. 

Capture.PNG

 

Note: Row 3 November to December number is the wrong result. 

 

Thank you so you so much for looking into this.

Hi @Stuznet

 

Row3 = 
VAR Row1Sum = CALCULATE(DIVIDE([Total];2);FILTER(ALLSELECTED(Data);Data[Month Num] <= MAX(Data[Month Num]) && Data[Month Num] +1 >= MONTH(TODAY())))
VAR Row2Sum = CALCULATE(DIVIDE([Total];2);FILTER(ALLSELECTED(Data);Data[Month Num] <= MAX(Data[Month Num]) && Data[Month Num] +2 <= MONTH(TODAY()) && Data[ROW] <> BLANK()))
RETURN
Row1Sum + Row2Sum
Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


@mwegener

I could not thank you-you enough, I could have never thought of that approach. 

 

Thank you again 🙂 

Hi,

 

Sorry i cannot help with this one.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Stuznet,

 

maybe this helps.

 

Measure = IF(HASONEFILTER(Table1[Row]);SUM(Table1[Value]);CALCULATE(SUM(Table1[Value]);FILTER(ALLSELECTED(Table1);Table1[Month Num] <= Max(Table1[Month Num]))))

Cumulative Sum.png

 

Best regards,

 

Marcus

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


@mwegeber,
Thank you for providing your solution, that’s the correct value I’m looking for but I forgot to mention that in power bi, I created row 2 and 3 with a separate measures

Row 2 =
VAR abc = CALCULATE( [total] / 1000000
RETURN
IF(MAX( Table [Month Num] + 1 >= MONTH(TODAY()), abc , BLANK())



Row3 =
VAR def = CALCULATE( [total], Filter( Table, Table[ Date] <> BLANK())) / (1000000)
RETURN
IF(MAX( Table [Month Num] + 2 >= MONTH(TODAY()), def , BLANK()


When I plugged in the measure in your solution I’m not getting the result

Measure = IF(HASONEFILTER( Table[Row]), CALCULATE(
[Row2] + [Row3] , FILTER(
ALLSELECTED( Table) , Table[Month Num] <= MAX(Table[Month Num]))))



Could you please advise? Thank you

Hi @Stuznet

 

 

Measure = CALCULATE([Row2]+[Row3],FILTER(ALLSELECTED(Table1),Table1[Month Num] <= Max(Table1[Month Num])))

Cumulative Sum with Measures.png

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


@mwegener,

I wish I can share the data, but my work computer is pretty locked down. I did try the 2nd functions you provided, I'm not getting the correct results.

 

Is there a way I can write a Cumulative with Conditional? 

 

For Example, 

Measure = If [Row 3]  <> BLANK(), Sum [Row 2] that is not BLANK

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.