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.

View solution in original post

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.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 278 members 2,906 guests
Please welcome our newest community members: