Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
gempar
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
Phil_Seamark
Employee
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')
			)

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

Proud to be a Datanaut!

View solution in original post

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

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

Proud to be a Datanaut!

 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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.