cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Employee

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
Employee

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!

Helper I

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!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors