Thoughts on Sorting College Acceptances

[milestaub]

I use Power BI Desktop and basic DAX for financial information for my work but a local high school has asked me to slice and dice information  related to their College acceptance rates in Power BI.  There are multiple tables in the dataset but I'll focus on just this one.  A sample is below. 

I need 1 report to show the following columns

School      #Applied     #Accepted   #Denied   #Acceptance Rate

So, if you look at example, I would have a table that would show in Row 1:

Alabama A&M University    2,  2, 0, 100%

The school, 2 applied, 2 were accepted, 0 were denied, 100% acceptance rate.

Row 2 in this first report would be for Albion College, it would be 2, 1, 1, 50%.

A second report would show only the rows where Accepted = Yes and then would show:

School, Highest ACT Score, Lowest ACT Score, Average ACT Score Accepted  (ex. 20 kids applied to School A, the highest had an ACT of 29, the lowest 20, add them all and divide by the number of students = average of 24....).

The first report is more important than the second, so if the second is a bit pie-in-the-sky, then ok.

Any thoughts are greatly appreciated.  I don't work at the school, I help with fund raising and they're smart, hard-working inner city kids who are really trying to get ahead.

Miles

 

[fredzwilson]

They really need to sort them out ASAP. Maybe students need a  history dissertation for money help with their study. I believe that it will be an amazing thing for them to base their research on. I hope that they will only improve with the help of it.

[v-chuncz-msft]

@milestaub,

You may learn to add measures in Power BI Desktop.

https://community.powerbi.com/t5/Desktop/Power-BI-subscrating-Matrix-view/m-p/227119#M101170

[milestaub]

Hi Sam,

Thanks for responding and for the link. I use Measures and Calculated Columns often - but with numbers (like the example in your link, Revenue #s, Cost #s), whereas with this dataset,  I'm using text (University Names, Students) that need to first be counted and then those counts get brought into Measures.

I don't know the COUNT commands well.  For example, counting the number of students who were accepted to Alabama A&M University, the number that were rejected, total applied...those are all counts.  Then I can use the Divide command, ex. Acceptance Rate = Divide ([Accepted], [TotalApplied]).  Is there any trick to first "counting" and then use math?

Or am I making this more complicated than necessary?

Thanks,

Miles

[milestaub · Accepted solution]

Got it!  Really I justed used the New Quick Measure fields to create fields that "stored" counts for applied, accepted, denied, %accepted...Really nice.

Thanks,

Miles

[v-chuncz-msft]

@milestaub,

Glad to hear that. You may help accept the solution above. Your contribution is highly appreciated.

[milestaub]

Hi Sam,

How do I "accept the solution above?"

Thanks,

Miles

[v-chuncz-msft]

@milestaub,

Sign in and click "Accept as Solution" displayed left of the "Reply" button.

[milestaub]

I wonder if I'm on the right path...I copied the table in Edit Queries mode and then filtered on Result = Accepted and saved the table as 'Colleges - Accepted' and then copied the original table, filtered on Result = Denied, table is 'Colleges - Denied.'

Finally, I created a new table with simply Colleges = DISTINCT('College Results'[College]) and did the same with Students, so I could manage relationships easily.

This report gets me part way there, ex. the College name is from the list of Colleges, the Accepted column is Colleges - Accepted[Result] and the 3rd column is Colleges - Denied[Result].

I can quickly eye ball high levels of acceptance or denied and I can also export this data and complete the next columns (ex. Total Applied = Accepted + Denied, % accepted) in Excel, but I'd prefer to stay in Power BI.  Given that the Accepted Column and the Denied Column are text fields that get counted, I don't know how to get a numerator with the [Count of Accepted] divided by another text column that would get totaled, [Total Applied.] 

I'm learning PBI and DAX so if you just want to guide me and force me to figure out that answer, I'm open to that as well.  It's really fun fiddling with data in PBI, but I'm not a programmer.

Thanks,

Miles

[milestaub]

No one has replied.  Is it because I should be able solve the problem (ok, I'm open to it with some guidance) or I'm leaving out important information?  I don't know where else to go to get help on this.

Miles