topic Re: Make averagex to work properly with dimensions in DAX Commands and Tips

Make averagex to work properly with dimensions

Anonymous — Tue, 28 Feb 2023 09:48:36 GMT

I have my measure where I am trying to display an average... The average should change by month and of course take into account BLANKS.

So, if I have data:

June: 3

July: 4

August: BLANK

I want to see (for the selected month):

June: 3

July: 3,5

August: 2,333

To achieve this I use CALCULATE ( [Users Avg Months] ,DATESYTD('Date'[Date] ,"31/05") )

It works fine with TOTALS!! ; but when I drag "user type" from Dimension it works wrong.

My code is:

Users:=CALCULATE(SUM(Users[Actual]), FILTER('User Type', ' User Type'[C Or A] IN {"A"} ))

Users Avg Months:= AVERAGEX ( VALUES('Date'[DW_SK_Date]),[Users] )

tamerj1 — Tue, 28 Feb 2023 10:13:12 GMT

Hi Anonymous
Please try

Users Avg Months := AVERAGEX ( VALUES ( 'Date'[DW_SK_Date] ), COALESCE ( [Users], 0 ) )

Anonymous — Tue, 28 Feb 2023 13:40:04 GMT

I get an error saying I cant use COALESCE. (The engine is SSAS 2019)

tamerj1 — Tue, 28 Feb 2023 14:25:07 GMT

Anonymous

Try

Users Avg Months :=
AVERAGEX ( VALUES ( 'Date'[DW_SK_Date] ), [Users] + 0 )