topic Re: Sum total of a column in a matrix until a row of another column is null or cero in DAX Commands and Tips

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

cnitscher — Mon, 29 Jun 2020 12:25:01 GMT

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

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

kentyler — Mon, 29 Jun 2020 14:09:49 GMT

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.

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

cnitscher — Tue, 30 Jun 2020 07:54:43 GMT

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

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

cnitscher — Thu, 02 Jul 2020 14:50:43 GMT

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

Thank you very much!

Claudio

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

AntrikshSharma — Thu, 02 Jul 2020 15:56:20 GMT

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?

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

cnitscher — Mon, 06 Jul 2020 16:52:11 GMT

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!

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

Thejeswar — Tue, 07 Jul 2020 07:52:44 GMT

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,