How to use the calculation "Count" so only rows with an amount different from 0 is counted


I need to use the calculation "count" for counting rows in a table - but the syntax should make sure only rows with amounts different from "0" are counted. 

What should the syntax be, when all rows must to be visible?


Heidi Frederiksen




  • Hi Heidi

    Maybe you can use an extra row to help you out.

    You could add a calculated column that counts 1 if the value in the row is different from 0 using the syntax: " if sum(d-1,0,m1) = 0 then 0 else 1 "

    where sum(d-1,0,m1) of course should point at the column you are interested in (so it would be different if it is a messure m2 or a calculation etc.)

    And then you just need to do a new calculated column og row that sums all the rows in the above. Ex syntax sum(c1,all,m1).

    Does it make sense?

    (You probably already know, that when using the advanced calculation you can just click at the columns to get the correct syntax for these).

  • Louise's comment is usually how I'd approach this too!


  • I agree with Louise and Tyler. 

    This is a general approach that I use very often - making intermediate calculations that are "flags" identifying something - and then making my final calculations based on the intermediate(s).

    Hope you were able to solve your issue Heidi :-)


Please sign in to leave a comment.

Didn't find what you were looking for?

New post