cancel
Showing results for
Did you mean:
Frequent Visitor

## Help creating a new percentage measure: countif equivalent to generate a percentage

Hi, Hoping someone can help me with a formula for PowerBI which I would usually use countif for in excel.

I have a column containing results as either "yes" or "no".  I need to calculate a new measure - the percentage of "no" values.

In excel I would use a count of "no", divided by the count of all values:

=COUNTIF(C1:C1006,"No")/COUNTA(C2:C1006)

How would I write a similar formula in Power BI?  What are the equivalent functions?

Thank you!

1 ACCEPTED SOLUTION Microsoft

Hi @gempar

Something like this should be close.  Just upcate the Table and ColumnName to your own column and give it a whirl

```Measure = DIVIDE(
COUNTROWS(FILTER('Table','Table'[ColumnName]="No")),
COUNTROWS('Table')
)```

Proud to be a Datanaut!

3 REPLIES 3 Microsoft

Hi @gempar

Something like this should be close.  Just upcate the Table and ColumnName to your own column and give it a whirl

```Measure = DIVIDE(
COUNTROWS(FILTER('Table','Table'[ColumnName]="No")),
COUNTROWS('Table')
)```

Proud to be a Datanaut!

Frequent Visitor

Hey @Phil_Seamark,

I've tried this measure & it doesn't seem to work when the pivot table rows are from a different table. Every row shows the same total. It does work when aggregating using fields from the same table as the Driving Local YN column (Trips).

- The Expense Customer PRO ID field in the pivot table image is from a different table called 'Trip Expenses'.

- The Driving Local YN column has the values Y, N or blank, so I used COUNTA for the denominator so it won't count blank values and skew the numbers.

- The tables are linked together in the model by a Trip ID column.

I've tried everything on the internet and can't get it to work. Any help would be immensely appreciated.

Let me know if you need any more information to help & thank you for your time.  Frequent Visitor

Thank you very much!  