Count occurrences of unique values with equal timestamps

[Anonymous]

Good afternoon,

Let me preface this with one of the key metrics in transportation that I'm currently trying to track is how many dispatches(Load IDs) are being dispatched at a single timestamp. 

For some context:

- A trip ID is a trip a driver has been assigned with.

- A load ID is a singular movement within a specific trip

For example: 1 trip, might have numerous load IDs as a driver may have several stops/deliveries to make in a trip.

Based off the picture above, 3 unique Load IDs were dispatched at one time (1/1/2021 9:16:26 AM) as well as 5 other instances where only 1 unique Load IDs were dispatched at one time.

Question: How do I calculate the amount of unique Load IDs have been dispatched at one time?

- The answer should be higher than 1, currently the average amount of Load IDs per trip is around 1.6. This calculation should lie somewhere in the middle of these two values. 

Thanks in advance,

JP

[v-robertq-msft]

Hi, @Anonymous 

According to your description, I can roughly understand your requirement, I’ve also followed your sample data to create some test data, you can try this measure:

Loads per Trip =

VAR _summarize=

SUMMARIZE('Table',[Trip ID],[dispatchedtime],"Count",COUNT('Table'[Load ID]))

return

AVERAGEX(_summarize,[Count])

Then you can create a table chart and place this measure:

According to your expected output, it seems that the data is grouped by your [userID], so you can add the [userID] column in fornt of the measure in your table chart.

And you can get what you want.

You can download my test pbix file below

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[Anonymous]

Hey Denis, thanks for the timely response!

This is the result I'm getting

[selimovd · Accepted solution]

Hey @Anonymous ,

try the following measure in a table with TripID and the dispatchedtime:

Dispatched at one time =
VAR vGroupByTable =
    GROUPBY(
        myTable,
        myTable[TripID],
        myTable[dispatchedtime],
        "@DateTime", SUMX( CURRENTGROUP(), 1 )
    )
RETURN
    SUMX(
        vGroupByTable,
        [@DateTime]
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic