Visualize on a card the busiest hour of the day when tickets are created

[MyThumbsClick]

Hi

I have a simple table that contains IT tickets logged. My table looks like below. I am trying to visualize the busiest hour of day that tickets are logged. So I created a new column using Groups – Bin with hourly bin sizes (timecreatedbins-hours).

TicketID	datecreated	timecreated		timecreatedbins-hours
1	01/01/2024	15:31		15:00
2	01/01/2024	09:55		09:00
3	02/01/2024	11:59		11:00
4	03/01/2024	14:33		14:00
5	03/01/2024	09:01		09:00

What I now want to do is visualize on a card the busiest time ie 09:00 (which has 2 tickets logged in that hour). I would also really like to display this as 09:00-09:59 on the card (instead of just 09:00).

I have tried to do this with topn but am failing miserably.

[Anonymous]

Hi @MyThumbsClick ,

I made simple samples and you can check the results below:

Duration = 
VAR _START = FORMAT('Table'[hours],"hh:mm")
VAR _END = FORMAT(TIME(HOUR('Table'[hours]), 59,00),"hh:mm")
RETURN
COMBINEVALUES(" - ",_START,_END)

Result = var _t = ADDCOLUMNS('Table',"count",COUNTAX(FILTER(ALL('Table'),[Duration]=EARLIER([Duration])),[Ticket]))
var _max = MAXX(_t,[count])
RETURN COMBINEVALUES("Times / ",_max,CALCULATE(SELECTEDVALUE('Table'[Duration]),FILTER(_t,[count]=_max)))

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[akhaliq7]

Try the following, create a new calculated table with the following dax code:
TOPN(
    1,

    ADDCOLUMNS(

        VALUES( 'Table'[timecreatedbins-hours]),

        "Total Rows", COUNTROWS('Table')
    )

)

Then place the bin groups column in the card to change the formatting I would use dynamic format strings or create a new calculated conditional column using power query or dax

[MyThumbsClick]

Thanks. Im not sure a Calculated Table will work for me as i need the card visual to react to the slicers on the report and I dont think this is possible?

[akhaliq7]

Try

CALCULATE(

    COUNTROWS('Table'),

    TOPN(

        1,

        ALLSELECTED('Table'[timecreatedbins-hours]),

        COUNTROWS('Table'),
        DESC
        ),

        VALUES('Table'[binscreatedbins-hours])
    )

[MyThumbsClick · Accepted solution]

Thanks for your help akhaliq7. This is what i have ended up with and works well:

tickets_average-perhour_perday = 
VAR __Table = 
    GROUPBY(
        'AllTickets',
        [created_at_date],
        "__Count",COUNTX(CURRENTGROUP(),AllTickets[created_at_time-bins])
    )
RETURN
  AVERAGEX(__Table,[__Count])+0