How to calculate average percentage in Power BI

Question

Right now I have a set of data in PowerBI as shown in the screenshot above. I have a Measure to calculate the % of OK:

total_student = COUNT(StudentAns[Name])

ok_% = 
VAR OK_COUNT = COUNTROWS(
    FILTER(
        StudentAns,
        StudentAns[Answer] = "OK"
    )
)

RETURN (OK_COUNT/StudentAns[total_student])

I created a Matrix to show the % of OK for each month as shown in the screenshot below:

The average percentage for all of the months is what I'm looking for. As a result, the final output response should be 89.05 percent, which is calculated as the average of 85.95 percent, 91.4 percent, 89.27 percent, and 89.58 percent.

Because I want to use the result as a Target Goals for KPI visualisation, I want to get the average % of OK throughout all of the months.

Answer 1

You can add one more measure to the matrix as follows:

ok_2 % =
IF(
    HASONEVALUE( 'StudentAns'[Month] ),
    [ok_%],
    AVERAGEX(
        VALUES( StudentAns[Month] ),
            [ok_%]
    )
)

It calculates your original measure for each month, but it returns the average of your measure's results for the Totals.

HASONEVALUE is a function that returns a value. True if the filtered context contains just one distinct value; AVERAGEX calculates the average of a series of expressions evaluated in each row; VALUES provides a list of distinct values.

If you are interested in learning more about Power BI, then check out the affordable Power BI certification cost now!