Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Sum total of a column in a matrix until a row of another column is null or cero



I want to get the total of a column in a matrix but only until a row in another column is blank or cero.


How can I do that?


Thank you community!



Solution Sage
Solution Sage

You can write a measure that tests if the other row is blank, or equals zero, and only return a total if the other row has a number. Every cell in a Power BI report is calculated independently, unlike a spreadsheet where cells often depend on each other. If you need help with the DAX please create a small sample table in excel, or in a power bi file and make it available and I'll help you with the coding.

Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!

Join the conversation at We Talk BI find out more about me at Slow BI

Hi @kentyler , did you have the chance to have a look at this?


Thank you very much!



What do you mean by "But for these fieldS that are stock, I need the next row after the SALES TY is blank whics is W202021" if the row of SALES TY is blank then it will return blank, do you want something else there in STOCK RT LY?



For column B,C,D I need the sum until the last row in the B column is no blank.

For column, D,E,F, I need to sum until the first row in the B column is blank.


You can see what I need in the row 33.


Thank you very much!

Hi @cnitscher 

You can achieve this using the below columns and measures.


Step 1: Create 2 columns as shown below

Sales(with zeros for future) = IF(Sheet1[Sales this Year] = 0, 0, Sheet1[Sales Last Year])

Stocks(with zeros for future) = IF(Sheet1[Stock TY] = 0, 0, Sheet1[Stock LY])


Step2: Create the below 4 measures 

Total Sales this year = SUM(Sheet1[Sales this Year])
Total Sales last year = SUM(Sheet1[Sales(with zeros for future)])
Total Stocks this year = SUM(Sheet1[Stock TY])
Total Stocks last year = SUM(Sheet1[Stocks(with zeros for future)])

 Notice that the newly created columns are used in the measure


The Output screenshots are given below



When these measures are used in the table, they look as shown below



If this is what you are lookng for, pls. mark this as solution and appreciate with a kudos!!


Hi @kentyler, I would really appreciate if you can help me with the DAX.


Please find the link of the excel Example  with a short explanation of what I need.


Thank you in advance.



Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors