- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Cumulative Sum of 2 Rows

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-06-2018 08:38 PM

Hi guys,

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

Solved! Go to Solution.

Accepted Solutions

## Re: Cumulative Sum of 2 Rows

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-07-2018 11:45 PM

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

All Replies

## Re: Cumulative Sum of 2 Rows

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-06-2018 11:34 PM

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

Best regards,

Marcus

## Re: Cumulative Sum of 2 Rows

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-07-2018 05:54 AM - edited 12-07-2018 05:38 PM

@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

## Re: Cumulative Sum of 2 Rows

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-07-2018 11:13 AM

Hi @Stuznet

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

## Re: Cumulative Sum of 2 Rows

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-07-2018 05:37 PM - edited 12-07-2018 05:40 PM

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

## Re: Cumulative Sum of 2 Rows

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-07-2018 06:53 PM

Hi,

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

## Re: Cumulative Sum of 2 Rows

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-07-2018 08:07 PM - edited 12-07-2018 08:11 PM

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.

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

Thank you so you so much for looking into this.

## Re: Cumulative Sum of 2 Rows

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-07-2018 08:11 PM

Hi,

Sorry i cannot help with this one.

## Re: Cumulative Sum of 2 Rows

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-07-2018 11:45 PM

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

## Re: Cumulative Sum of 2 Rows

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-08-2018 04:45 AM

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

Thank you again