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

[Anonymous]

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!

[Ashish_Mathur]

Hi,

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

[Anonymous · Accepted solution]

@Anonymous  - 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.