Forum Discussion
Measure Efficiency -- Get distinct values, average per single date, then sum the averages
Sorry, this doesn't quite get there either. It doesn't account for there being duplicate TLG[Workday Days] per Date Worked & Employee.
Probably helps to give an example. Starting dataset:
| Employee ID | Date Worked | Workday Days | TLP ID |
| 491 | 01/05/20 | 0 | 10 |
| 491 | 01/06/20 | 0.5 | 4 |
| 491 | 01/06/20 | 0.5 | 941 |
| 491 | 01/07/20 | 1 | 331 |
| 491 | 01/08/20 | 1 | 596 |
| 491 | 01/08/20 | 1 | 2537 |
| 11402 | 01/06/20 | 1 | 319 |
| 11402 | 01/06/20 | 1 | 782 |
| 11402 | 01/07/20 | 1 | 331 |
| 11402 | 01/08/20 | 1 | 841 |
| 99821 | 01/05/20 | 0 | 10 |
| 99821 | 01/06/20 | 1 | 319 |
| 99821 | 01/07/20 | 1 | 2537 |
| 99821 | 01/07/20 | 1 | 596 |
| 99821 | 01/07/20 | 1 | 941 |
| 2729 | 01/05/20 | 1 | 331 |
| 2729 | 01/05/20 | 1 | 1931 |
| 2729 | 01/06/20 | 0.5 | 841 |
Distinct values of Employee ID, Date Worked, & Workday Days:
| Employee ID | Date Worked | Workday Days |
| 491 | 01/05/20 | 0 |
| 491 | 01/06/20 | 0.5 |
| 491 | 01/07/20 | 1 |
| 491 | 01/08/20 | 1 |
| 11402 | 01/06/20 | 1 |
| 11402 | 01/07/20 | 1 |
| 11402 | 01/08/20 | 1 |
| 99821 | 01/05/20 | 0 |
| 99821 | 01/06/20 | 1 |
| 99821 | 01/07/20 | 1 |
| 2729 | 01/05/20 | 1 |
| 2729 | 01/06/20 | 0.5 |
AVERAGE Workday Days by day:
| Date Worked | Average Workday Days |
| 01/05/20 | 0.333 |
| 01/06/20 | 0.750 |
| 01/07/20 | 1.000 |
| 01/08/20 | 1.000 |
Lastly, the SUM of averages, making the expected result of the measure here:
| 3.083 |
Thanks for providing sample data and output. This measure should work for you in a table with Dates on the rows.
NewMeasure =
VAR __summary =
ADDCOLUMNS (
SUMMARIZE ( TLG, TLG[Employee ID], TLG[Date Worked] ),
"AvgHrs", CALCULATE ( AVERAGE ( TLG[Workday Days] ) )
)
RETURN
AVERAGEX ( __summary, [AvgHrs] )
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Greatly appreciate the reply! Unfortunately this measure is in an SSAS tabular model, so I need it to work in all contexts and can't limit to a table with dates on the rows.