cancel
Showing results for
Did you mean:
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

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.

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!