Average Measure : matrix row and sub row calculation

[grapple_75]

Hi

I have a table which lists Vehicle Type, Vehicle Id, Date, Journey Id and Journey Duration.

Each Vehicle Id completes multiple journeys across multiple dates.

I am need a measure that not only calculates the Average Total Duration by Vehicle ID but also at the higher Vehicle Type level.

My matrix visual looks something like this.......

I know the issue relates to the count of distinct dates which at Vehicle Type level equals 3, the same as each Vehicle ID.  I need the measure to take into account the number of vehicle ids when calculating the average at Vehicle Type level.

Any help will be greatly appreciated. Thanks.

[Anonymous]

Hi @grapple_75 ,

Thank you for sharing, this will help more people!!

Best regards,

Mengmeng Li

[grapple_75 · Accepted solution]

looks like I've now solved this using.....

Measure =

var _tbl1 = SUMMARIZE(Vehicle_Journey, Vehicle_Journey[vehicleId], Vehicle_Journey[StartDate],"a",sum(Vehicle_Journey[Journey Duration]))

var _average = averagex(_tbl1,[a])

return _average

have spent a whole day battling with this and then work it out within a few minutes of posting for help 🙄!