Forum Discussion

tcmem's avatar
tcmem
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...
Need Help
  • Vvelarde's avatar
    Vvelarde
    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

tcmem's avatar
tcmem
Regular Visitor
8 years ago

Here's an example of my data:

 

Field1Field2
Acct113
Acct2 
Acct31
Acct45
Acct5 
Acct63
Acct70
Acct812

 

Based on my criteria of counting Field1 where Field2 is <= 12 and NOT BLANK, the results should be a count of  5.

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

Hi tcmem,

 

This will also work

 

=CALCULATE(COUNTROWS(Data),Data[Field2]<=12)-COUNTBLANK(Data[Field2])

 

Hope this helps.

  • tcmem's avatar
    tcmem
    Regular Visitor
    8 years ago

    Hello Ashish,

     

    Are you able to use two different Fields in this function? I need to remove blanks in Field1 & Field2.

     

    Thanks for your help!

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      8 years ago

      Hi,

       

      Subtract another COUNTBLANK() with the other field