cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
walkerb32
Helper II
Helper II

Calculate Column Iterating Subtotal Column

Hello All!

I am trying to calculate a column of the subtotal of revenue by a year-month column:

 

Yr-Mo    Revenue      SubTotal Revenue
2021-01  1,000,000    1,000,000
2021-02  1,000,000    2,000,000
2021-03  1,000,000    3,000,000
2021-04  1,000,000    4,000,000
2021-05  1,000,000    5,000,000

 

I've tried a number of functions and always returns 5,000,000 for every row.  

 

Thanks in advance!

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi  @walkerb32 ,

Use the following measure:

revenuetotal = CALCULATE(SUM('Table'[Revenue]),FILTER(ALL('Table'),'Table'[Yr-Mo]<=MAX('Table'[Yr-Mo])))

then create a new column:

v-luwang-msft_0-1622531821525.png

 

 

 

 

Wish it is helpful for you!

 

Best Regards

Lucien

View solution in original post

8 REPLIES 8
v-luwang-msft
Community Support
Community Support

Hi  @walkerb32 ,

Use the following measure:

revenuetotal = CALCULATE(SUM('Table'[Revenue]),FILTER(ALL('Table'),'Table'[Yr-Mo]<=MAX('Table'[Yr-Mo])))

then create a new column:

v-luwang-msft_0-1622531821525.png

 

 

 

 

Wish it is helpful for you!

 

Best Regards

Lucien

View solution in original post

Awesome!  Thanks; didn't even know  that was a thing!

Fowmy
Super User
Super User

@walkerb32 

Try like this:

Sub Total Revenue = 

CALCULATE(
    SUM(Table3[Revenue   ]),
    FILTER( ALL(Table3[Yr-Mo   ]), Table3[Yr-Mo   ] <= MAX(Table3[Yr-Mo   ] ))
)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@Fowmy thank you for the quick reply.  It is returning the same value in the row for revenue.

@walkerb32 

 

Can you share a screenshot of the result?

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@Fowmy can you send your results as well?  This is really tricky.

@walkerb32 

Please find attached the file.

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Data Sample.png

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors