Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

Average of Months

I have Date table and FactTable and are related on DateId.

I want to get avg of all months from the factTable.

I have a measure called CountID = DistinctCount(FactTable[ID])

When place in the table visual by month, it looks like this

timEalll_0-1664272824657.png

I am having issues creating avg of months, the logic is the total months which is 11147 divided by no of months 8 since jan to august the answer is 1393
I was using this dax but it didn't work

Avgof all month =
AVERAGEX(
	KEEPFILTERS(VALUES('Date'[Month Name Short])),
	CALCULATE([Detection Count])
)

How can i get this in dax so it looks like blue circle below

timEalll_1-1664273134817.png

thanks

 

1 ACCEPTED SOLUTION

The problem is the month sort order column, we're not removing the filters on that. Try

Average all = CALCULATE( AVERAGEX( VALUES('Date'[Month name short]), [Detection count]), REMOVEFILTERS('Date'))

View solution in original post

5 REPLIES 5
johnt75
Super User
Super User

Try

Avgof all month =
AVERAGEX ( ALL ( 'Date'[Month Name Short] ), [Detection Count] )
Anonymous
Not applicable

@johnt75  - it didn't work. the measure gives the same result as the screenshot above (as count measure)

In the visual are you using the month from the Date table or the fact table? It should be the date table

The problem is the month sort order column, we're not removing the filters on that. Try

Average all = CALCULATE( AVERAGEX( VALUES('Date'[Month name short]), [Detection count]), REMOVEFILTERS('Date'))
Anonymous
Not applicable

@johnt75 I'm using Month from the date table

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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