Forum Discussion
DAX Formula: For each Row, Count the Cells Whose Sum = 0
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....
Anonymous ,
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
3 Replies
v-chuncz-msftCommunity Support
Anonymous ,
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
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.