measure time average for line and clustered chart

[tomb0]

im trying to make a line and clustered chart
that shows Date to average lead time.

lead time pid = CALCULATE(FORMAT(AVERAGE(PID[Leadtime]),"HH:MM:SS"),

PID[Reason Description]="Unplanned Physical Inventory"||PID[Reason Description]="Putaway Physical Inventory")

the Calculate formula i can do in the filter of the chart to. but then it also not work.
the only place i can put the measure is in the Tooltips

many thank in advance
Tom,

[Anonymous · Accepted solution]

Hi @tomb0,

Format function will convert the target value to text with specific text formats.
AFAIk, text value can't be aggregated with common math modes(e.g. sum, average, max, min...), so power bi will forced use 'count' mode to summary these type of values if you use them on the chart value field which only allows the aggregated values.

BTW, current power bi data model tables do not support duration type and you can't do aggregate on the date or time values, I'd like to suggest you convert them to 'total second' values then you can simply do aggregate on them.

Aggregating Duration/Time - Microsoft Power BI Community

Regards,

Xiaoxin Sheng