cancel
Showing results for
Did you mean:
Highlighted

## Average Count by hours

Hi,

New to PBI and wondering how I can calculate the average number of cases by hour. Data is a list of cases and I've created a measure Count = countrows(Incident). Through this I can see the number of cases by month (mes) and hour (Hora):

What I'd like is to show the average amount of cases per hour, i.e. Average of Cases per Hour at 06:00 as per screenshot would be 39+49/2 = 44.

I have tried using averagex, summarize, calculate and create new table but not having any luck so far.

Any suggestions as to how this could be achieved?

Many thanks!

Matt

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion

## Re: Average Count by hours

try with this:

```Avgxhora =
VAR HoraCreacion =
FIRSTNONBLANK ( Table1[Hora Creación], Table1[Hora Creación] )
RETURN
DIVIDE (
CALCULATE ( COUNTROWS ( Table1 ), ALLEXCEPT ( Table1, Table1[Hora Creación] ) ),
COUNTROWS (
SUMMARIZE (
FILTER ( ALL ( Table1 ), Table1[Hora Creación] = HoraCreacion ),
Table1[Hora Creación],
Table1[Mes Creación]
)
)
)```

Lima - Peru
4 REPLIES 4
Highlighted
Community Champion

## Re: Average Count by hours

try with this:

```Avgxhora =
VAR HoraCreacion =
FIRSTNONBLANK ( Table1[Hora Creación], Table1[Hora Creación] )
RETURN
DIVIDE (
CALCULATE ( COUNTROWS ( Table1 ), ALLEXCEPT ( Table1, Table1[Hora Creación] ) ),
COUNTROWS (
SUMMARIZE (
FILTER ( ALL ( Table1 ), Table1[Hora Creación] = HoraCreacion ),
Table1[Hora Creación],
Table1[Mes Creación]
)
)
)```

Lima - Peru
Highlighted

## Re: Average Count by hours

That is absolutely fantastic, thank you very much for taking the time to help me with this one, it worked perfectly.

I need a lot more practice with this it seems 🙂

Saludos,

Matt

Highlighted
Frequent Visitor

## Re: Average Count by hours

There will be some way to do this in direct query??????

Highlighted
Community Champion

## Re: Average Count by hours

@jorgeaguirre

Try this;

Lima - Peru

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors