Forum Discussion
Calculate Dynamic average time
In my tabe i have each employees timgings for each day.
i would like to calculate the average timings for each employee as time range changes.
sample data:-
| Emp ID | Time |
| 136225 | 9:07:31 AM |
| 136225 | 9:36:45 AM |
| 136225 | 9:42:04 AM |
| 136225 | 9:42:11 AM |
| 136225 | 9:42:49 AM |
| 136225 | 9:44:49 AM |
| 136225 | 9:46:44 AM |
| 136225 | 9:48:23 AM |
| 136225 | 9:50:23 AM |
| 136225 | 9:51:27 AM |
| 136225 | 9:53:49 AM |
| 136225 | 9:54:15 AM |
| 136225 | 9:56:39 AM |
| 136225 | 10:05:49 AM |
| 136225 | 10:18:34 AM |
| 136225 | 10:43:22 AM |
| 136225 | 12:35:58 PM |
I have tried several formulas but i couldnt get what i am expecting.
1:-
AvgTime = CALCULATE(
FORMAT(AVERAGE('Table'[Time]),"HH:MM:SS"),
ALLEXCEPT('Table','Table'[Emp ID]))But it is giving me the overall average for that perticular employee irrespective of the date range change from the slicer which is a column from the same table.
IF i change the range in begin_date slicer then the values wont change accordingly.
2:-
AvgTime = CALCULATE(
FORMAT(AVERAGE('Table'[Table]),"HH:MM:SS"),
ALLSELECTED('Table'))For above dax, it is giving me the average time if i select a perticular id .
but if i choose multiple ids from slicer then it is giving me the above values that is cumulative average for those selected ids.
How can i get the average for each indivudual employeees average time which gets changes as the date range values changes.
Please help.
Mohan V.
- Anonymous8 years ago
Got by myself..
Below query worked.
AvgTime = CALCULATE(FORMAT(AVERAGE('Table'[Time]),"HH:MM:SS"),FILTER(ALLEXCEPT('Table','Table'[Emp ID]), 'Table'[begin_date] <= MAX('Table'[begin_date])))
1 Reply
- AnonymousNot applicable
Got by myself..
Below query worked.
AvgTime = CALCULATE(FORMAT(AVERAGE('Table'[Time]),"HH:MM:SS"),FILTER(ALLEXCEPT('Table','Table'[Emp ID]), 'Table'[begin_date] <= MAX('Table'[begin_date])))