How to count rows and work out percentages

[Anonymous]

Hi

I have a table of data 'Department_Allocation' that shows employee name, department, job description and employee number. I also have a table 'Absence_Data' that shows department, employee number, absence start date, absence end date, absence reason.

Each time an employee is absent, a new line is created in the 'Absence_Data' table with the employee number, department, start & end date and absence reason.

I need to work out how many people have been absent in each department, as a % so i can display it in a matrix.

For example, I know I have had 7 people absent in Department A and that there are 22 people in the department so the % would be 31.8% and in Department B there have been 4 absences and 18 people in the department so the % would be 22.2%

What I can't seem to figure out though, is what measures/columns I would need to create to get the answers. In excel, this is a pretty straight forward set of formulas but I'm struggling in Power BI.

Could anyone help?

Many thanks

James

[Anonymous · Accepted solution]

I have figured out what I needed to do. It was actually very simple.

I needed to create a measure to count the number of employees the business has using the 'Department_Allocation' table

No. of Employees = COUNTA(Department_Allocation[Employee Number])

I then created a measure to calculate the number of employees that have taken absence by using a count of employee ID's in the 'Absence_Data' table.

No. of Staff Taking Absence = DISTINCTCOUNT('Absence_Data'[Employee ID])

Finaly I created a measure to work out the overall percentages.

% of Staff Absent = [No. of Staff Using Absence]/[No. of Employees]

The relationship between the departments in each table did the work for me in terms of giving me the percentage for each department.

Hope this helps anyone that has a similar issue in the future.

[v-yuta-msft]

@Anonymous,

To be general, you can create a measure to achieve abcense rate by each department, then create a matrix visual and drag department column to row fields, drag job description column to coolumn field and drag the measure to the value field.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Anonymous

hello, could you provide a picture of what you are talking about, pictures of your datasets