Forum Discussion

Regular Visitor

8 years ago

Solved

How to include Zeros in column value count

I'm trying to calculate a count of items in a column that are less than or equal to 12. For some reason, it's excluding the Zeros. How do I include the Zeros in my count? I'm also excluding blanks ba...

8 years ago

Hi, you can use:

Measure =
COUNTROWS (
    FILTER ( Table1, ISBLANK ( Table1[Field2] ) = FALSE () && Table1[Field2] <= 12 )
)

Measure =
CALCULATE (
    COUNT ( Table1[Field2] ),
    FILTER ( Table1; ISBLANK ( Table1[Field2] ) = FALSE () && Table1[Field2] <= 12 )
)

Regards

Victor

Lima - Peru

Regular Visitor

8 years ago

Thanks for your reply! I would also like to exclude blanks from 'Table1'[Field1] as well.

Icon for Super User rank

Super User

8 years ago

Column = IF( Table1[Column1] <> BLANK () , CALCULATE( COUNT ( Table1[Column1]), Table1[Column2] <= 12 || Table1[Column2] = BLANK () ))

tcmem
Regular Visitor
8 years ago
Unfortunately, neither suggestion worked. I created another table which pulls all fields from the original table, but excludes the rows where Field1 is BLANK. This eliminates one needed filter. However, it's still not including the Field2 = Zeros in the total count with Field2 <=12. I created this formula to count Field1 if Field 2 is NOT BLANK and is <= 12.

Less Than 12 or Blank = CALCULATE(COUNT(Table1'[Field1]),'Table1'[Field2] <= 12 && 'Table1'[Field2] <> BLANK())

If this would include the occurrences of Field2 = 0 in the count, this would yield the results I'm trying to achieve.

Thanks for your help thus far!
- tcmem
  Regular Visitor
  8 years ago
  Here's an example of my data:
  
  Field1 Field2
  Acct1 13
  Acct2
  Acct3 1
  Acct4 5
  Acct5
  Acct6 3
  Acct7 0
  Acct8 12
  
  Based on my criteria of counting Field1 where Field2 is <= 12 and NOT BLANK, the results should be a count of 5.
  - Vvelarde
    Community Champion
    8 years ago
    tcmem
    
    Hi, you can use:
    
    Measure = COUNTROWS ( FILTER ( Table1, ISBLANK ( Table1[Field2] ) = FALSE () && Table1[Field2] <= 12 ) )
    
    Measure = CALCULATE ( COUNT ( Table1[Field2] ), FILTER ( Table1; ISBLANK ( Table1[Field2] ) = FALSE () && Table1[Field2] <= 12 ) )
    
    Regards
    
    Victor
    Lima - Peru