Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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

 

 

Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    hi Anonymous

     

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

4 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    hi Anonymous

     

    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]
            )
        )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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

    • jorgeaguirre's avatar
      jorgeaguirre
      Frequent Visitor
      9 years ago

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