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

Re: Average Count by hours

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

Re: Average Count by hours

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

Re: Average Count by hours

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

Highlighted
Frequent Visitor

Re: Average Count by hours

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

Highlighted
Community Champion
Community Champion

Re: Average Count by hours

@jorgeaguirre

 

Try this;

 

DQ Options.png




Lima - Peru

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

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