Average Count by hours

[Matt_Alexander]

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

[Vvelarde · Accepted solution]

hi @Matt_Alexander

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

[jorgeaguirre]

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

[Vvelarde]

@jorgeaguirre

Try this;

[Matt_Alexander]

Hi @Vvelarde

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