Calculating percentage

[Anonymous]

Hi All, 

I've been trying to calculate attendance percentage based on 26 weeks of attendance for a new attendance report. The old report gives employees who have been here less than 26 weeks a credit for all the weeks they were not employed. However, my report for example, if an employee has only been here 7 weeks and has attended 6 weekly meetings, they have an attendance percentage of 85%, whereas in the old report their percentage is calculated as 25/26, 96% 

How can I achieve this using powerBI and dax. 

I have determined that each meeting missed is a loss of 3.84% from a total of 100%

Thank you, 

[Mrxiang · Accepted solution]

To calculate attendance percentage based on 26 weeks of attendance in Power BI using DAX, you can use the following formula:

Attendance Percentage = (Total Weeks Attended / 26) * 100
To account for employees who have been here less than 26 weeks, you can use the following formula:

Attendance Percentage = ((Total Weeks Attended + (26 - Total Weeks Employed)) / 26) * 100
Here's an example DAX formula that you can use in Power BI to calculate attendance percentage based on the above formula:

Attendance Percentage = 
VAR TotalWeeksAttended = SUM('Attendance'[Weeks Attended])
VAR TotalWeeksEmployed = SUM('Attendance'[Weeks Employed])
RETURN
IF(TotalWeeksEmployed >= 26,
    DIVIDE(TotalWeeksAttended, 26) * 100,
    DIVIDE(TotalWeeksAttended + (26 - TotalWeeksEmployed), 26) * 100
)
This formula first calculates the total weeks attended and total weeks employed for each employee. If the employee has been employed for 26 weeks or more, the formula divides the total weeks attended by 26 and multiplies by 100 to get the attendance percentage. If the employee has been employed for less than 26 weeks, the formula adds the number of weeks the employee was not employed (26 - Total Weeks Employed) to the total weeks attended and then divides by 26 to get the attendance percentage.

Note that the formula assumes that each meeting missed is a loss of 3.84% from a total of 100%. If this percentage changes, you will need to adjust the formula accordingly.

[Anonymous]

Thank you this worked! When I put the data into a matrix and group it by manager it adds up the percentages and I get very large values like 2334% attendance, any reccomendation to adjust these values to 100%?

Thanks again