Average in a 1-to-many relation

[jorikke]

I've got these two table:

Table1 (Drives)

drivenr

dep_station

dest_station

record_ID

is_punctual (0 or 1)

Table2 (Employees)

record_ID

emp_number

emp_division

unique_number (used to make every row in this table unique)

Because 1 drive can have multiple employees from different divisions on it, I've added an active 1:* relationship on  record_ID.

Now, I use AVERAGE(is_punctual) to calculate the percentage of punctual drives (grouped by division). That's working fine for drives with only 1 employee on it. But when 2 employees of the same division are on the drive, "is_punctual" will only be counted once (should be double because there are 2 matching empolyee-records). Example:

Drive 123 from A to B has record_id "123AB" has an is_punctual of 1

Drive 123 from B to C has record_id "123BC" has an is_punctual of 0

Employee 567 with division X was on both record_id's

Employee 678 with division X was only on record_id "123BC"

Expected result of AVERAGE(is_punctual): 66% (2/3)

Actual result: 50% (1/2 because "123AB" isn't counted double)

How can I fix this? 

[spg_vizcube]

Hi @jorikke 

Which table you are doing the average into, it could be due to filtering applied by the model

could you please share the model

Appreciate your Kudos!

If I solve your problem, please accept this as a Solution

Add me on Linked In , Visit my blog vizcube.biz
Subscribe to my youtube channel 

[mahoneypat · Accepted solution]

Hard to be sure without example data tables, but please try this expression in a measure.

Pct Punctual = DIVIDE(CALCULATE(COUNTROWS(Employees), Drive[is_punctual] = 1), COUNTROWS(Employees))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[jorikke]

Altough it's still strange PowerBI won't add the numbers correctly in this relationship, this solution does give me the correct numbers. I wasn't a big fan of this solution because I had to edit multiple measures to make this working for all of my number but it's working. Thanks!

[MFelix]

Hi @jorikke ,

How are you calculating the AVERAGE value?

Can you please share your model but with data in it, for your example how will the two tables be filled.