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

[cnitscher]

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

[kentyler]

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.

[cnitscher]

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

Thank you very much!

Claudio

[AntrikshSharma]

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?

[cnitscher]

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!

[Thejeswar]

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

Regards,

[cnitscher]

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.

Claudio