Forum Discussion

dlgardo's avatar
dlgardo
Regular Visitor
9 years ago
Solved

count numeric column cells and exclude certain value

I would like to count the number of cells in a numeric column but exclude if the value is 0.  Seems like this should be fairly easy, but since I am new to PowerBI, I would appreciate any assistance! ...
  • alanhodgson's avatar
    alanhodgson
    9 years ago

    Hey dlgardo,

     

    Sean's answer works for me. This also seems to work:

     

    Count Column = COUNTX(FILTER('Table', 'Table'['Column'] <> 0),[Column])

    Sean can you confirm?

     

    Thanks,

     

    Alan

  • Sean's avatar
    Sean
    9 years ago

    Yep that's another way of doing the same thing...

     

    the only thing I'll change is add the table name as per Rob Collie's post here

     

    EDIT: Sorry the post was actually by MattAllington on Rob's site

     

    • Column reference:  Table[Column Name]
    • Measure Reference:  [measure name]

    But that's just a suggestion it should work regardless :smileyhappy:

     

    Count Column = COUNTX ( FILTER ('Table', 'Table'[Column] <> 0) , 'Table'[Column] )

     

alanhodgson's avatar
alanhodgson
Icon for Solution Supplier rankSolution Supplier
9 years ago

Hey dlgardo,

 

Create a new column like the one below:

 

Count Column = SUMX([Dataset], IF([Column1]<>0, 1, 0))

 

This will count all the fields that are not equal to 0.

 

Hope this helps,

 

Alan

Sean's avatar
Sean
Icon for Community Champion rankCommunity Champion
9 years ago

Here's an alternative

 

Column Count Measure = CALCULATE ( COUNTROWS('Table'), 'Table'[Column] <> 0 )

The above is basically internally treated like this...

 

Column Count Measure = CALCULATE ( COUNTROWS('Table'), FILTER ( 'Table', 'Table'[Column] <> 0 ) )

Also if for some reason you neeed to exclude the zeros but include the blanks just add the countblank like this

 

Column Count Measure = CALCULATE ( COUNTROWS('Table'), 'Table'[Column] <> 0 ) + COUNTBLANK ( 'Table'[Column] )

 

  • alanhodgson's avatar
    alanhodgson
    Icon for Solution Supplier rankSolution Supplier
    9 years ago

    Hey dlgardo,

     

    Sean's answer works for me. This also seems to work:

     

    Count Column = COUNTX(FILTER('Table', 'Table'['Column'] <> 0),[Column])

    Sean can you confirm?

     

    Thanks,

     

    Alan

    • Sean's avatar
      Sean
      Icon for Community Champion rankCommunity Champion
      9 years ago

      Yep that's another way of doing the same thing...

       

      the only thing I'll change is add the table name as per Rob Collie's post here

       

      EDIT: Sorry the post was actually by MattAllington on Rob's site

       

      • Column reference:  Table[Column Name]
      • Measure Reference:  [measure name]

      But that's just a suggestion it should work regardless :smileyhappy:

       

      Count Column = COUNTX ( FILTER ('Table', 'Table'[Column] <> 0) , 'Table'[Column] )