Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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
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
thanks
Solved! Go to 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'))
Try
Avgof all month =
AVERAGEX ( ALL ( 'Date'[Month Name Short] ), [Detection Count] )
@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'))
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
217 | |
89 | |
76 | |
66 | |
60 |