Dividing a Sum by a Distinct Count, then applying a filter

[Keegs27]

I'm trying to visualise some data which is a sum of defect scores for a number of units that have been audited/assessed.  Each unit can have multiple defects (1 unit to many defects) and units are audited/assessed daily so the data is reviewed daily, weekly, monthly, quarterly, annually etc.  The defects are also associated with a department, respsonsible for creating it and therefore rectifying it.

I have the following measure:

AAA - Score divided by distinct count =

DIVIDE(

    SUM('Raw Data'[DefectScore]),

    DISTINCTCOUNT('Raw Data'[Unit ID])

)

I can then filter this to show a particular month.  This bit works and gives me the total sum of all defect scores for the month divided by the distinct count of units in the month e.g. if the defect scores is 100 and 50 units were audited/assessed then the averager score per unit is 2.0.

The next bit I want to be able to do is split this average scores into the contributions that each department made.  This is where it doesn't work.  I think applying the filter only takes into account the units that a specific department had a defect on and therefore the distinct count is incorrect (it should be a distinct count of all the units from the month but it is clearly a smaller number).

 I've put together the table below to help illustrate:

Unit ID	Defect Code	Defect Score	Department
Unit 1	AB1	1	Dept A
Unit 1	AB2	5	Dept B
Unit 1	AB3	1	Dept C
Unit 1	AB4	1	Dept A
Unit 2	AB1	1	Dept A
Unit 2	AB3	1	Dept C
Unit 3	AB2	5	Dept B
Unit 3	AB7	1	Dept C
Unit 3	AB9	10	Dept B
Unit 4	AB8	1	Dept D
Unit 5	AB3	1	Dept C
Unit 5	AB4	1	Dept A

Sum of the defect score = 29

Distinct unit count =5

Average score per unit = 5.8

I then apply a filter (or try to display using a pie chart) and I think the following happens:

Sum defect score for Dept A = 4

Distinct unit count = 5

Average score per unit = 0.8

However, Dept A only have defects on x3 distinct units and I think BI uses the number 3 as the distinct unit count and the answer I get is 4/3 = 1.33

Any help on how to stop the distinct count being reduced when the department filter is added would be useful. 

[Keegs27]

That worked perfectly.  Thank you.

[DataInsights · Accepted solution]

@Keegs27,

Try this measure:

AAA - Score divided by distinct count = 
DIVIDE (
    SUM ( 'Raw Data'[Defect Score] ),
    CALCULATE (
        DISTINCTCOUNT ( 'Raw Data'[Unit ID] ),
        ALL ( 'Raw Data'[Department] )
    )
)

---