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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
another1here
Frequent Visitor

Finding percentage of instances of one word in an Excel column

Hi - I am trying to set up a query in Desktop that will help me find the following from an Excel spreadsheet that I keep updated with fresh data regularly, which resides online in a OneDrive Business folder.

 

I have a number of people (candidates) who are interviewing for jobs. I am trying to determine Offer Acceptance Rate (percentage) which is determined by Hires / (Hires + Declined). 

 

The data is arranged as seen in the image here. 

 

 

PowerBI Excel Query OAR.jpg

 

In other words, I only care about those who received offers, and I want to know what percentage accepted those offers.

 

So, therefore, I need to disregard all words *other than* Hired and Declined and use only those two words to determine the percentage of times "Hired" appears out of the total Hired + Declined. 

 

Maybe I don't need to do this via a query? 

 

Ultimately, I will need to see what this percentage is across the whole company, but then I will also need to break it down by team (column B) as well. And then of course by time (quarter, year). 

 

Ideally the end result could also be plotted on a line chart to show how the acceptance rate changed over time. 

 

Thanks in advance for the community's help with this. Apologies in advance if I'm overthinking this and it's super simple! 🙂

1 ACCEPTED SOLUTION

Sorry, I should have been more detailed!  I meant to add it as a Measure in your model.  The dialog you're showing is to add it as a custom column in your query.  Instead of that, go to the Modeling tab in the main window and click "New Measure":

 

ModelingTab.png

 

Then, enter the formula in the formula bar.  You'll need to give your measure a name, so the full formula will actually look something like this:

 

PercentHired = COUNTROWS(FILTER(YourTableName, [Status]="Hired"))/COUNTROWS(FILTER(YourTableName, [Status]="Hired" || [Status]="Declined"))

View solution in original post

8 REPLIES 8
JeffDuzak
Microsoft Employee
Microsoft Employee

Try this measure:

 

=COUNTROWS(FILTER(YourTableName, [Status]="Hired"))/COUNTROWS(FILTER(YourTableName, [Status]="Hired" || [Status]="Declined"))

@JeffDuzak Thank you for such a quick reply!

 

I have tried this... making sure I'm doing this correctly though... I receive an error "Token comma expected". Screenshot below.

 

It's entirely likely that I'm doing this all wrong :). 

 

Thank you again!

 

OAR attempt 1.jpg

Sorry, I should have been more detailed!  I meant to add it as a Measure in your model.  The dialog you're showing is to add it as a custom column in your query.  Instead of that, go to the Modeling tab in the main window and click "New Measure":

 

ModelingTab.png

 

Then, enter the formula in the formula bar.  You'll need to give your measure a name, so the full formula will actually look something like this:

 

PercentHired = COUNTROWS(FILTER(YourTableName, [Status]="Hired"))/COUNTROWS(FILTER(YourTableName, [Status]="Hired" || [Status]="Declined"))

Also, I should mention that having this calculation be a measure means that it will automatically recalculate in any context you are in (that's the beauty of measures).  You mentioned breaking it down by team.  You can create, for example, a Matrix visual, and add to it your Team column and the PercentHired measure, and it will automatically calculate the PercentHired for each team.

Wow - I'm in data heaven. Thank you so much - this changes everything. I think I may become dangerous at work now 😉

 

So, is there a way to make this into a percentage if I'm viewing it in this way, for instance (see screenshot)? Instead of seeing .71, I much rather see 71%. 

 

Also, I've been having some fun breaking it down by team, etc... everything I needed to do! 

 

Thank you x 100. 

 

OAR attempt 2.jpg

Figured it out! I did it by using "Format" in the ribbon for the measure specifically, not in the formatting of the visual. 

 

I have learned a lot today. And I am a happy camper. Thank you so much to @JeffDuzak! Kudos to you!

Glad to hear it!

 

Yes, you can format as percentage:  Just select your measure, then click Modeling in the ribbon, and in the Formatting section, click the Format dropdown and choose Percentage.

Percentage.png

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.