'AVERAGE' measure produces "can't display the visual" error

[christianMDU]

I have only just begun using Power BI and I'm following along with some YouTube videos. I thought I had a good grip, so would create a visual off of my own data, but I am stuck!

I have a spreadsheet with a number of columns in. The most interesting ones are labelled "Item ID" (a unique reference) and "Time to Accept" which is hh:mm:ss. I want a card to show me the average time to accept value across each of my rows. I've selected the "Item ID" field and the "Time to Accept" fields which has produced this table:

I've then clicked Home -> New measure and entered "Average Time to Accept = AVERAGE(Sheet[Time to Accept])" but now the visual displays an error "Can't display the visual":

When I click "See details", here's what I see:

Here are the details:

Feedback Type:
Frown (Error)

Timestamp:
2022-05-17T11:29:00.5623758Z

Local Time:
2022-05-17T12:29:00.5623758+01:00

Session ID:
608b479d-a1e5-4b08-b725-36d987a57245

Release:
April 2022

Product Version:
2.104.702.0 (22.04) (x64)

Error Message:
MdxScript(Model) (4, 48) Calculation error in measure 'Sheet'[Average Time to Accept]: The function AVERAGE cannot work with values of type String.

OS Version:
Microsoft Windows NT 10.0.19042.0 (x64 en-US)

CLR Version:
4.7 or later [Release Number = 528372]

Peak Virtual Memory:
54.4 GB

Private Memory:
644 MB

Peak Working Set:
848 MB

IE Version:
11.789.19041.0

User ID:
668f0e5f-8f41-47ba-95d0-677693d7c8aa

Workbook Package Info:
1* - en-GB, Query Groups: 1, fastCombine: Disabled, runBackgroundAnalysis: True.

Telemetry Enabled:
True

Snapshot Trace Logs:
C:\Users\2005\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot0bd8c994-e380-433c-88db-f7a0df8054c5.zip

Model Default Mode:
Import

Model Version:
PowerBI_V3

Performance Trace Logs:
C:\Users\2005\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip

Enabled Preview Features:
PBI_enableWebView2
PBI_mobileAuthoringFormattingUI
PBI_useModernFormatPane
PBI_sparklines

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_dataPointLassoSelect
PBI_compositeModelsOverAS
PBI_enhancedTooltips
PQ_WebView2Connector
PBI_scorecardVisual
PBI_lineChartError

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
100%

Supported Services:
Power BI

Formulas:


section Section1;

shared Sheet = let
    Source = Excel.Workbook(File.Contents("...\sample.xls"), null, true),
    Sheet1 = Source{[Name="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Item ID", type text}, {"Created", type date}, {"Completion Date", type date}, {"Priority", type text}, {"Origin", type text}, {"Created By", type text}, {"SLA Exceeded", type logical}, {"SLA Name", type text}, {"SLA Stage", type text}, {"Owner", type text}, {"Assign To", type text}, {"Modified", type date}, {"Modified By", type text}, {"Department Name", type text}, {"Requester", type text}, {"Status", type text}, {"Incident Type", type text}, {"Category", type text}, {"Summary", type text}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type", {{"Priority", each Text.BeforeDelimiter(_, " "), type text}})
in
    #"Extracted Text Before Delimiter";

shared #"Errors in Sheet" = let
Source = Sheet,
  #"Detected Type Mismatches" = let
    tableWithOnlyPrimitiveTypes = Table.SelectColumns(Source, Table.ColumnsOfType(Source, {type nullable number, type nullable text, type nullable logical, type nullable date, type nullable datetime, type nullable datetimezone, type nullable time, type nullable duration})),
    recordTypeFields = Type.RecordFields(Type.TableRow(Value.Type(tableWithOnlyPrimitiveTypes))),
    fieldNames = Record.FieldNames(recordTypeFields),
    fieldTypes = List.Transform(Record.ToList(recordTypeFields), each [Type]),
    pairs = List.Transform(List.Positions(fieldNames), (i) => {fieldNames{i}, (v) => if v = null or Value.Is(v, fieldTypes{i}) then v else error [Message = "The type of the value does not match the type of the column.", Detail = v], fieldTypes{i}})
in
    Table.TransformColumns(Source, pairs),
  #"Added Index" = Table.AddIndexColumn(#"Detected Type Mismatches", "Row Number" ,1),
  #"Kept Errors" = Table.SelectRowsWithErrors(#"Added Index", {"Item ID", "Created", "Completion Date", "Priority", "Origin", "Created By", "SLA Exceeded", "SLA Name", "SLA Stage", "Owner", "Assign To", "Modified", "Modified By", "Department Name", "Requester", "Status", "Incident Type", "Category", "Summary"}),
  #"Reordered Columns" = Table.ReorderColumns(#"Kept Errors", {"Row Number", "Item ID", "Created", "Completion Date", "Priority", "Origin", "Created By", "SLA Exceeded", "SLA Name", "SLA Stage", "Owner", "Assign To", "Modified", "Modified By", "Department Name", "Requester", "Status", "Incident Type", "Category", "Summary"})
in
  #"Reordered Columns";

I know I must be doing something silly. Any ideas?!

[christianMDU]

Thanks @tamerj1. I had to post this again as it was marked as spam and the other post has taken on a life of its own 🙂

[tamerj1 · Accepted solution]

Hi @christianMDU 

the time column cannot be of a text or Geneal data type. It should be of date or date time data type. Otherwise just return it as integer ( number of seconds ) then you can convert after summing or averaging to hh:mm:ss format.