cancel
Showing results for
Did you mean:
Highlighted
Member

Calculate Average based on distinct count of values in a column

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?

Wes

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

Re: Calculate Average based on distinct count of values in a column

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 🙂

Proud to be a Datanaut!

3 REPLIES 3
Super Contributor

Re: Calculate Average based on distinct count of values in a column

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 🙂

Proud to be a Datanaut!

Member

Re: Calculate Average based on distinct count of values in a column

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

Super Contributor

Re: Calculate Average based on distinct count of values in a column

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

Proud to be a Datanaut!

Announcements

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.