cancel
Showing results for
Did you mean:
Highlighted
Anonymous
Not applicable

## 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.

Mohan V.

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

## Re: Calculate Dynamic average time

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])))```
Anonymous
Not applicable

## Re: Calculate Dynamic average time

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])))```

Announcements

#### Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors