Need average of usage for each serial number

[hamzashafiq]

Hi Folks,

I need your help to calculate Usage formula for each "Serial Number". The formula for Total Usage I have currently built is just subtracting the Max Hours Meter Reading with Minimum Hours Meter Reading. It works fine if the Model has only one Serial Number, however if the Model has multiple serial number (Please see the serial number column in Screenshot), I need to calculate the Max-Min for each Serial Number and then calculate the average to get "Total Usage". Currently the Total Usage is 693 (884-191) but it should be 119(884-765) and 16(207-191) and the average of 119 and 16 should be 67.5 (Average Usage).   Please see the attached screenshot for the data I have. Please see the blue highlighted columns.

[hamzashafiq]

It's working, thank you!

[hamzashafiq]

It's giving me syntax error.

[johnt75 · Accepted solution]

You need to summarize by serial number and then work out the average of the individual values, something like

Total Usage = AVERAGEX(
ADDCOLUMNS( SUMMARIZE( TableName[Serial Number] ), 
    "@usage", CALCULATE( MAX( TableName[Meter Hours]) - MIN( TableName[Meter Hours] ) )
),
[@usage]
)