Count formula

[timward10]

Hi, 

I want to count the number of times a field has a number entered, from January to Dec. In excel, I would just use a count formula and reference the columns, what should I use within PBI? 

Thanks

[bhanu_gautam]

@timward10 , Use this

CountNumbers = COUNTROWS(
FILTER(
YourTable,
ISNUMBER(YourTable[YourField]) &&
YourTable[DateColumn] >= DATE(Year, 1, 1) &&
YourTable[DateColumn] <= DATE(Year, 12, 31)
)
)

[timward10]

Table is attached, I would expect to see all of them columns return zero, apart from the ones that have numbers in, they would return one and so on. 

What fields are you referencing in your formula? 

Thanks 

[Anonymous · Accepted solution]

Hi @timward10 ,

Firstly make sure the data type of your columns is whole number.

Then you can create a Table visualization and add columns you need with Count Function.

Power BI will show blank if there is no data. 

If you want to show 0 instead of blank, you need to create 12 measures due to your data model.

The measures are similar, they look like as below.

Count Number Jan 25 = CALCULATE(COUNT('Table'[Total Rev Jan 25]))+0

...

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[timward10]

Perfect @Anonymous! It was just the formula I was looking for, if I add the columns together this works how I need it to! Thank you!