cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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')
			)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
Microsoft
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')
			)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

 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.

 

 

driving local 2.png

 

driving local.png

Thank you very much!

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors