Hello, I have table that stores the progress of employee at on day in a single record:
Emp | Data | No.Cases | Delayed
Mr. A 21/10/2019 3 0
Mr. B 21/10/2019 2 1
Mr.A 20/10/2019 5 1
Mr.C 20/10/2019 7 0
Mr.B 20/10/2019 8 1
Mr.D 18/10/2019 12 1
Then I have two Slicers: 1. for the employee names 2. for the Date period
I need to calculate thier Delay Scores based on thier on average on that period and the averge of all employee that same period.
Average of specific employee in a specifc period:
I have created a measure that will calculate the averge of each indvuldae that period, and it is working fine when I want to know the averge delay of a specific employee in a specific period using this Dax:
(CALCULATE (
AVERAGE ('Table1'[Delayed]),
FILTER ( ALLSELECTED ( 'Table1' ), 'Table1'[EMPIDM] = MAX ( 'Table1'[EMPIDM] ) )
))
What is needed is the Total averge of all employee which changes when changing the Date filter but doesn't change when selecting one employee. Which will be the Average of the Average: (Sum of Average of specific employee/no. employee who has records that period).
For example considering the above table:
-The Average delay of Mr. A in the period 20-21/10 is: 0.5 (since he is has only one delay in two days) Done using the Dax above
-The Average delay of Mr. B in the period 20-21/10 is: 1 (since he is delayed in both 2 days) Done using the Dax above
-The Average delay of Mr. C in the period 20-21/10 is: 0 (He has no delays) Done using the Dax above
Now what I want is Average of the averages in 21-21/10:
Which will be (0.5 + 1 + 0)/3 = 0.5. (will ignore the the Employee names filter and will consider only the Date filter). How can this be done?
It would be great help. Thank you.
