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 based on two different columns. The results would be correct if the Zeros were included in the count.

 

I'm attempting to create a new measure because I need to do a calulation using the results. Please see an example below.

 

Less than 12 or Blank = CALCULATE(count('Table1'[Field1)]),'Tabel1'[Field1)]<>BLANK(),'Table1'[Field2]<>BLANK(), 'Table1'[Field2]<=12,'Table1'[Field2]=0)

 

I'm really new to using Power BI on this level. Any suggestions are appreciated.

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

10 Replies

  • pxg08680's avatar
    pxg08680
    Icon for Resolver III rankResolver III
    8 years ago

    tcmem

    Left table is the raw data with zeroes and blank values and right side table shows you count with blank values also.

     

     

     

     

    • tcmem's avatar
      tcmem
      Regular Visitor
      8 years ago

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

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

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