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.

Are you ready to take your data visualization skills to the next level? Join our Power BI Training Course and learn how to transform your raw data into interactive and insightful reports!