cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

calculate average of a measure

HI,

I am not sure how to get the average of a measure.

Currently: Total = count (table1[ID])

And I have the following:

I would like to get the average of the all these months to show just one number say in a KPI report: 29K

1 ACCEPTED SOLUTION
Community Champion

@Anonymous

Try with:

Average = AVERAGEX(VALUES(Table1[Date].[Month]),[Total])

Regards

Victor

Lima - Peru
4 REPLIES 4
Super User

Hi @Anonymous,

Assuming that:

1. There is a calendar table with dates ranging from the first date in your dataset to the last date in your dataset
2. There is a month and year column in the calendar table using the FORMAT(Calendar[Date],"MMMM") and YEAR(Calendar[Date]) functions
3. There is a relatioship from the Date column in your dataset to the Date column in your calendar table
4. Write the following calculated field formula

=AVERAGEX(SUMMARIZE(Calendar,Calendar[Month],"ABCD",COUNT(Table1[ID])),[ABCD])

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Champion

@Anonymous

Hi, try with

Average = AVERAGEX(VALUES(Table1[Category]),[Total])

Regards

Victor

Lima - Peru

Lima - Peru
Anonymous
Not applicable

Do I have to do something with the [Category]?

My data fields are "date", ID"

So currently, the return result doesn't work out.

Community Champion

@Anonymous

Try with:

Average = AVERAGEX(VALUES(Table1[Date].[Month]),[Total])

Regards

Victor

Lima - Peru

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors