powerbiaveragepercentagekpi

How to calculate average percentage in PowerBI?

enter image description here

Hi everyone,

I'm still new to PowerBI, 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:

enter image description here

What I want to find is the average percentage for all the months. So the final output answer should be 89.05%, which is the average of 85.95%, 91.4%, 89.27% and 89.58%.

The reason I want to get the average percentage of OK across all the months is because I want to use the output as a Target Goals for KPI visualization.

Any help or advise will be greatly appreciated!

Solution

  • 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 for the Totals it returns the average of results of your measure.

    HASONEVALUE returns True if there is only one distinct value in the filtered context; VALUES - creates a list of unique values; AVERAGEX - calculates the average of a set of expressions evaluated in each row.