cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

Hi,

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!

Claudio

6 REPLIES 6
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

Frequent Visitor

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

Thank you very much!

Claudio

Super User
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?
Frequent Visitor

Hi,

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!

Community Champion

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!!

Regards,

Frequent Visitor

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.

Claudio

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors