cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tahira_ali_28
Frequent Visitor

to calculate sum of column on filtered rows

Hi,

 

I have a table with multiple columns but i want to performa calculation on 2 columns. here is the sample data.

 

I want to find the last non zero value in column A and then get the sum of all the values in column B starting from Last non-zero value of column A till all the rows with 0 values in column A. In short, calculation needs to be performed on highlighted area only.

 

Please let me know how can i do that. Appreciate any help.

 

Thanks in Advance

 

 

tahira_ali_28_0-1634132894039.png

 

 

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@tahira_ali_28 

Please create the measures as follows:

A_S Last = 
VAR _LastIndex =  CALCULATE( MAX ( Table1[Index] ) , Table1[A_S] <> 0 )
RETURN
CALCULATE(
    SUM(Table1[D]),
    Table1[Index] > _LastIndex
)

 



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

View solution in original post

2 REPLIES 2
Fowmy
Super User
Super User

@tahira_ali_28 

Please create the measures as follows:

A_S Last = 
VAR _LastIndex =  CALCULATE( MAX ( Table1[Index] ) , Table1[A_S] <> 0 )
RETURN
CALCULATE(
    SUM(Table1[D]),
    Table1[Index] > _LastIndex
)

 



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

View solution in original post

Thanks alot @Fowmy .. This solution solved my problem.

 

Thanks for your help.

 

Regards

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