Calculate Average based on distinct count of values in a column

[wes-shen-poal]

Hi there,

I am currently using a data table called 'VMS VehicleEvent', where each row is a unique event for a given Vehicle ID. A single Vehicle ID can have multiple unique events (thus a Vehicle ID can be in multiple rows in the table).

e.g. As seen in the screenshot below, VehicleID 2714602, has a count of 9 VehicleEventIDs (or 9 unique events, or 9 rows).

Each Vehicle ID has only one Dwell Time value, and in the case for VehicleID 2714602 it is 1.32

My main goal is to work out the Average Dwell Time for all Vehicle IDs, which Power BI calculates it to be 5.99

Unfortunately this is incorrect.

What Power BI has done is that it multiplies the Dwell Time of each Vehicle ID by the number of rows the Vehicle ID is in.

e.g. VehicleID 2714602, Dwell Time = 9rows * 1.32 Dwell Time = 11.91

It does this for each Vehicle ID, and then sums it all up to get Total Dwell Time = 1283298.08

And then divides it by total number of rows = 214149

1283298.08/214149 = 5.99

But the calculation I am after is sum of Dwell Time for each Vehicle ID (i.e. 1.32+2.41+1.80..etc) divide by disctinct count of Vehicle IDs. Which should give me the correct Average Dwell Time of 5.52

Currently, my Dwell Time is a calculated column in data table with the formula:

Dwell Time = 'VMS VehicleEvent'[Port Exit Date]-'VMS VehicleEvent'[Vessel Depart Date]

Note: there is also only ONE Port Exit Date and ONE Vessel Depart Date for each VehicleID, thus, giving me only ONE Dwell Time for each VehicleID regardless of how many rows the VehicleID has in this table. See screenshot below:

Can someone kindly tell me what I need to do to achieve my desired Average Dwell Time calculation, which needs to be based on VehicleID level rather than at VehicleEventID (or row) level?

Thanks in advance for your help

Wes

[OwenAuger · Accepted solution]

Hi @wes-shen-poal

You can use AVERAGEX to calculate a simple average of an expression evaluated for each distinct value in a particular column.

In this case, you could define a measure like this:

Average of Dwell Time Equally Weighted by VehicleID =
AVERAGEX (
    VALUES ( 'VMS VehicleEvent'[VehicleID] ),
    CALCULATE ( AVERAGE ( 'VMS VehicleEvent'[Dwell Time] ) )
)

There is a separate question of how you could structure your data to avoid repeating the Dwell Times unnecessarily, but the above measure should work with your current table.

Regards,

Owen 🙂

[wes-shen-poal]

This is perfect! Thank you. Just curious, please can you provide me the link to the discussion on data structure you mentioned?

Thanks again.

Wes

[OwenAuger]

That's good.

Sorry I didn't have a link to a specific discussion; just meant that you might want to look at how the data is structured, as you appear to have data at different levels of granularity. It looks a bit like a header-detail type structure in your case. You might find some articles online. I know it is discussed in this book.

In your case, it looks like the Dwell Time is at the VehicleID level of granularity, but presumably some other information is at the VehicleEventID level of granularity.

Cheers,

Owen