cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
James_Bockett Frequent Visitor
Frequent Visitor

How to count rows and work out percentages

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

1 ACCEPTED SOLUTION

Accepted Solutions
James_Bockett Frequent Visitor
Frequent Visitor

Re: How to count rows and work out percentages

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.

3 REPLIES 3
Collin Member
Member

Re: How to count rows and work out percentages

@James_Bockett

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

Community Support Team
Community Support Team

Re: How to count rows and work out percentages

@James_Bockett,

 

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.

James_Bockett Frequent Visitor
Frequent Visitor

Re: How to count rows and work out percentages

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.