Average Measure : matrix row and sub row calculation

Question

Hi&nbsp;I have a table which lists Vehicle Type, Vehicle Id, Date,&nbsp;Journey Id and Journey Duration.Each Vehicle Id completes multiple journeys across multiple dates.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;I am need a measure that not only calculates the Average Total Duration by Vehicle ID but also at the higher Vehicle Type level.&nbsp;My matrix visual looks something like this.......&nbsp;&nbsp;&nbsp;&nbsp;I know the issue relates to the count of distinct dates which at Vehicle Type level equals 3, the same as each Vehicle ID.&nbsp; I need the measure to take into account the number of vehicle ids when calculating the average at Vehicle Type level.&nbsp;Any help will be greatly appreciated. Thanks.&nbsp;

grapple_75 · Accepted Answer

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 🙄!

Forum Discussion

Average Measure : matrix row and sub row calculation

2 Replies

Related content

DAX Measure for Ranking the valid rows and calculating the average rating

DAX Calculated Measure

calculate average of calculated measure

Calculating Weighted Average

Calculating average 12 month average