cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate III
Advocate III

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

 

Captura.PNG

 

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

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



Lima - Peru

View solution in original post

4 REPLIES 4
Highlighted
Community Champion
Community Champion

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



Lima - Peru

View solution in original post

Highlighted

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

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

Highlighted

@jorgeaguirre

 

Try this;

 

DQ Options.png




Lima - Peru

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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