cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

Capture.PNG

 

 

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:

 

Capture2.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

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

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 🙂



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
OwenAuger Super Contributor
Super Contributor

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

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 🙂



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

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

OwenAuger Super Contributor
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

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

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!

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?

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.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors