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

Dividing number of employees with specific rate against total active headcount from other data table

I am working on calculating the rate variance for each month but I am having trouble trying to calculate the monthly count of noncompliant rates with the monthly total count of active employees.

 

The total count of active employees is calculated from a headcount data table. The count of noncompliant rates is calculated from a rate variance data table.   I have to show the data for the past 12 months. 

 

I currently have the following data for the past two months: 

 

May 18

17 non compliant rates

164 total active headcount

 

June 2018 

19 non compliant rates

215 total active headcount

 

NC Rate Count = Count(NonCompliant Rates)

Total Active Headcount = CALCULATE(COUNTX(Headcount,'Headcount'[ID]),FILTER(ALLSELECTED('Headcount'),'Headcount'[Actual Status] = "1"), FILTER(Headcount, Headcount[Tracking Month]))

1 = active

0 = expired

Headcount above the rate  % = NC Rate Count / Total Active Headcount

 

My Total Active Headcount gives me a total of 379 for each months and as the total.    It does not calculate each month's total separately when I create the measure for Total Active Headcount in my rate variance data table.  But the monthly totals are correct in the headcount data table with the same Total Active headcount.  But I need to be able to calculate the Headcount above the rate % for each month (19/215 for June and 17/164 for May.  
I currently have a many-to-many relationship for my tables bc I have multiple data tables with unique data that are used across each other.  

 

Can someone please advise how I can calculate the HC above the rate % for each month? 

 

Thanks!

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Dividing number of employees with specific rate against total active headcount from other data t

@mrangelWG  - I think you need to start with your data model - fact tables with many-many relationships can cause some problems. It is better to filter both tables by a shared dimension table, such as Date. The date table would have a 1-Many relationship with each fact table.

 

Secondly, the Active Headcount measure is unnecessarily complicated. It could be done like this:

Total Active Headcount = 
CALCULATE(
    COUNTROWS(Headcount),
'Headcount'[Actual Status] = "1"
)
I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
Nathan Peterson

 

 

View solution in original post

2 REPLIES 2
Super User
Super User

Re: Dividing number of employees with specific rate against total active headcount from other data t

@mrangelWG  - I think you need to start with your data model - fact tables with many-many relationships can cause some problems. It is better to filter both tables by a shared dimension table, such as Date. The date table would have a 1-Many relationship with each fact table.

 

Secondly, the Active Headcount measure is unnecessarily complicated. It could be done like this:

Total Active Headcount = 
CALCULATE(
    COUNTROWS(Headcount),
'Headcount'[Actual Status] = "1"
)
I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
Nathan Peterson

 

 

View solution in original post

Super User
Super User

Re: Dividing number of employees with specific rate against total active headcount from other data t

Hi,

Share the download link of your PBI file.  To avoid confusion, please share only the required Tables in that file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 158 members 1,741 guests
Please welcome our newest community members: