How to Ignore Granularity of Dataset

[kmes912]

I have data that is aggregated as DATE>UNIT>DEPT>CODE>JOB then value for regular hours and scheduled hours, like below.

Date	Unit	Dept	Unit Dept	Code	Job	Reg	Sch
1/29/2021	12345	0010	12345-0010	5678	JOB 1	8	8
1/29/2021	12345	0011	12345-0011	567	JOB 2	8	16
1/29/2021	12345	0011	12345-0011	567	JOB 3	8	2
1/29/2021	12345	0012	12345-0012	8	JOB 4	8	20
1/29/2021	12345	0013	12345-0013	3	JOB 5	0	0
1/29/2021	12345	0014	12345-0014	2	JOB 6	0	8

I'm trying to do a lot of different things, and I dont know if I need to do a summarized table to get there, or there is a way within a measure to aggregate differently.

First thing, I want to ignore the job & code detail, that doesnt matter to me. I need to keep analysis at the UNIT-DEPT combo, so I made that as a column merged.

First I took a DISTINCTCOUNT of UNIT-DEPT column, to show the total of how many combinations they are, with FILTER >0 for reg or >0 in sch so I in essence ignore the department 0013 with 0,0. 

Second I took a DISTINCTCOUNT of UNIT-DEPT column, to show the total of how many combinations they are, with FILTER >0 for reg AND >0 in sch so I can get a count for dept 0014 as that is considered a bad dept (1st minus 2nd equals this flagged situation).

My problem becomes when I want to aggregate the remaining values. Depts 0010, 0011, 0012, I want to test how they perform when comparing REG HRS & SCH HRS. I want to know how many departments are in the range of <90%, >110% if you take REG/SCH. Whenever I do the math: 

CALCULATE(DISTINCTCOUNT('Table'[Unit Dept]),FILTER('Table',[Actual vs Schedule %]<.9)), I end up with a big value, I believe because it's still retaining the code & Job level.

Do I need to do a summarize table to get to this? Is there a way to within the existing structure? 

[lbendlin]

You don't need any aggregations or summarized tables. The Power BI data model and the measures can do all that work for you.

You say you want to do this by dept and unit.  But your sample data has the same unit.  PLease provide better sample data.

[kmes912]

Yes, it has millions of lines. It has a year of dates and 900 units and tons of unit/dept combinations. I was just trying to show in the sample data that 0011 shows 2x but I need to aggregate the (8+8) reg (2+16) sch to come to 16/18 for the value for that dept, then evaluate what range it falls into.