DAX Formula: For each Row, Count the Cells Whose Sum = 0

[roncruiser]

Hi,

I've been working on and off on this one for awhile now but it has me duped.  It seem like it should be simple.  It probably is, but i cannot get it.   I keep getting the count of cells that contain zero which is not what I need.  BTW, I'm looking for a DAX formula.

File contained here:

https://drive.google.com/file/d/1tFF3WBXw8oUbkD92BiSUooqS4UYUVnoq/view?usp=sharing

Look in tab labeled "Pivot Table":

Objective:

For each row, count the cells whose sum = 0.  In other words, for each vref setting, count the cells whose sum = 0.

(Not to be confused with the count of cells that contain zero.)  So, along the right side of the table, there will be column that shows the count of cells with the sum = 0.

Thanks....

[v-chuncz-msft · Accepted solution]

@roncruiser ,

You may check if the post below helps.

https://community.powerbi.com/t5/Desktop/Need-count-for-matrix-non-zero-values/m-p/406361#M186090

[roncruiser]

I'll post another query to capitolize on the solution.

It requires counting zeroes on the left and on the right of the zero CDC (Coarse+Fine) reference. Thanks again.

[roncruiser]

@v-chuncz-msft 

Your solution did help!  Thank you.

Based on the thread you pointed to.  Specific to my application the solution is:

=IF (
HASONEVALUE ( raw_data[CDC (Coarse+Fine)]),
SUM ( raw_data[Value] ),
COUNTROWS (
FILTER ( VALUES ( raw_data[CDC (Coarse+Fine)]), CALCULATE ( SUM ( raw_data[Value] ) ) = 0 )
)
)

Columns for both Subtotals and Grandtotals need to show.

Thank you.