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

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 Community Champion

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

Frequent Visitor

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

@jorgeaguirre

Try this; Lima - Peru Announcements #### Power Platform Community Conference

Check out the on demand sessions that are available now! #### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella. Top Solution Authors
Top Kudoed Authors
Users online (2,125)