cancel
Showing results 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

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

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

Resident Rockstar

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

#### Power BI Monthly Update - November 2023

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

#### Fabric Community News unified experience

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

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

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