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

to calculate sum on the column filtered data

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-1634040333931.png

 

 

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@tahira_ali_28 

Create the following measure:

Measure = 
VAR __LastNonBlank = LASTNONBLANKVALUE( Table1[A] , MAX(Table1[Index] ))
RETURN
CALCULATE(
    SUM(Table1[B]),
    Table1[Index] >= __LastNonBlank
)


File attached below  my signature



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

3 REPLIES 3
tahira_ali_28
Frequent Visitor

Hi @Fowmy , 

 

The above provided solution is not working for this data set. would you please help me with that?

 

what i understand after testing the above measure that MAX fucntion is not working properly, its not picking up the correct last non-zero value in a column. 

 

Your help in this matter is highly appreciated. Thanks in Advance!

 

tahira_ali_28_0-1634121451577.png

 

Fowmy
Super User
Super User

@tahira_ali_28 

Create the following measure:

Measure = 
VAR __LastNonBlank = LASTNONBLANKVALUE( Table1[A] , MAX(Table1[Index] ))
RETURN
CALCULATE(
    SUM(Table1[B]),
    Table1[Index] >= __LastNonBlank
)


File attached below  my signature



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

Thanks Fowmy, its fixed my issue.

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!

Top Solution Authors