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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Academic course pass rate

Hello every one,

I am new to Power BI because my institution just switched to it recently. I need help creating a measure that calculates course pass rate. This would represent the percent of courses marked with A, B, C (these grades are considered as pass grades). Courses are assigned letter grades of A,B, C, D, F or W. Below is an idea of what my table columns look like

Student ID      Course                Grade

1                   ENGL 101              B

2                   ENGL 341              D

2                   MATH 101            C

3                  PSYC 31                 A

3                  SOCI 123               F

 

Basically with this example I need to calculate the percent of pass grades (A, B, C) over the the total count of grades.

Any help will be greatly appreciated.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Anonymous 

Create a measure to retreive pass/total.

PASS_RATE_M =
VAR TOTAL_COUNT =
    COUNTROWS ( T22 )
VAR PASS_COUNT =
    CALCULATE (
        COUNTROWS ( T22 ),
        FILTER (
            T22,
            T22[Grade]
                IN {
                "A",
                "B",
                "C"
            }
        )
    )
RETURN
    PASS_COUNT / TOTAL_COUNT

2019-08-02 08_25_14-papercut - Remote Desktop Connection.png

 

Cheers!
A

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

What exact result are you expecting and why?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Mathur I'm trying to get the percent of courses students passed with a grade of A, B, or C on the total grades. I have a sample data layout above.

Hi,

What exact result do you want to see?  Show the expected result in a simple Table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
gckcmc
Resolver I
Resolver I

assuming you're choosing all grades as having equal value, then these are really just pass/fail percentages.  I'd create a column from the letter grades, which is a "pass/fail" column.  put your condition of a/b/c there, and make it a pass or fail value. (or a binary column called "pass?" to be even faster)

 

then you can create a measure to calculate pass %.

Anonymous
Not applicable

Hi @Anonymous 

Create a measure to retreive pass/total.

PASS_RATE_M =
VAR TOTAL_COUNT =
    COUNTROWS ( T22 )
VAR PASS_COUNT =
    CALCULATE (
        COUNTROWS ( T22 ),
        FILTER (
            T22,
            T22[Grade]
                IN {
                "A",
                "B",
                "C"
            }
        )
    )
RETURN
    PASS_COUNT / TOTAL_COUNT

2019-08-02 08_25_14-papercut - Remote Desktop Connection.png

 

Cheers!
A

Anonymous
Not applicable

Thank you for the quick response. I used the statement and only have to change the table name but I get the error message below:

Pass Rate = COUNTROWS('By Program')
VAR Pass Count = CALCULATE(COUNTROWS('By Program'), FILTER('By Program', [Grade] IN {"A", "B", "C", "P"}))

Return Pass Rate/[Pass Count]

 

pix.png

 

However, I decided to create two separate measures, one that adds pass grades:

Pass Count = CALCULATE(COUNTROWS('By Program'),FILTER('By Program', [Grade] IN {"A", "B", "C", "P"}))

and another that adds all grades: All Grades = COUNTROWS('By Program'), then do Pass Count/All Grades to get my desired results. 

What are my doing wrong in  the error message in order to have all three measures into one? 

Anonymous
Not applicable

@Anonymous 

Looks like your VAR name contains a spcae which is not alowed.

Pass Rate --> Pass_Rate

Pass Count --> Pass_Count

etc.

 

Thanks!
A

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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