Why is Average not an option for values?

[Kazzi]

Hello,

I am trying to show average time on my report. When adding time, I right click and under values, I have "count" and "count distinct" but not average. It used to show up and I'm not sure where it has gone? I've tried various visualizations but I am not getting it as an option. 

[Kazzi]

Thank you! It was stored as text

[burakkaragoz · Accepted solution]

Hi @Kazzi ,

Thanks for your question! The issue you're encountering is quite common and usually relates to the data type of the field you're trying to average.

Here's what to check:

1. Data Type of the Column

   • Go to Model view in Power BI.
   • Select the column you're trying to average.
   • Ensure it's set to a numeric or time duration data type (e.g., Decimal Number, Whole Number, or Duration).
   • If it's set as Text, Power BI won't offer aggregation options like Average.

2. Field Formatting

   • If you're working with time values (like durations), make sure the column is formatted as Duration, not Time or Text.

3. Use a Measure Instead\ If the column is complex or derived, you can create a measure manually:

   AverageTime = AVERAGE('YourTable'[YourColumn])

   Then use this measure in your visual.

4. Check for Hidden Fields\ Sometimes, fields are hidden from the report view. Make sure the column is visible and not marked as "Do not summarize."

For more on data types and aggregation, check out:
https://learn.microsoft.com/power-bi/transform-model/desktop-data-types
https://learn.microsoft.com/power-bi/transform-model/desktop-quickmeasures-average

Let me know if you'd like help checking the data type or writing a custom measure!

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

This response was assisted by AI for translation and formatting purposes.