Finding percentage of instances of one word in an Excel column

[another1here]

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. 

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! 🙂

[JeffDuzak]

Try this measure:

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

[another1here]

@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!

[JeffDuzak · 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":

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"))

[JeffDuzak]

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.

[another1here]

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. 

[another1here]

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!

[JeffDuzak]

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.

[JeffDuzak]