The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hello,
I have 2 different measures
1st one:
SMA 3 Months = AVERAGEX( WINDOW( -2, REL, 0, REL, SUMMARIZE(ALLSELECTED('Date'),'Date'[Month Number Of Year]) ), [Sum Sales Amount] )
2nd one (the correct one, the result I expected)
SMA 3 Months = AVERAGEX( WINDOW( -2, REL, 0, REL, SUMMARIZE(ALLSELECTED('Date'),'Date'[Month Number Of Year]) ), [Sum Sales Amount] )
This is how Date table looks like
Could you help explain why the 1st one does not work, but the second one works? I am aware that soring alphabetical would mess the result, but why the 1st one gives the sales amount for that month, but not an average (of 3 months, depending on sorting order)?
Thank you a lot!
Solved! Go to Solution.
You're using ALLSELECTED inside of an iterator. Understanding what happens in that situation is highly complex so I recommend avoiding that if possible (by defining it as a variable outside of the iterator, for example).
Ignoring that, I think the core problem here is that the table you define inside WINDOW doesn't have any corresponding outer value to define the current row in your first example. That is, your WINDOW table is trying to sort on [Month Number Of Year] but that column doesn't exist in your table.
I've not tested this but it might work better:
SMA 3 Months =
VAR _Months_ =
SUMMARIZE (
ALLSELECTED ( 'Date' ),
'Date'[Month Number Of Year],
'Date'[English Month Name]
)
RETURN
AVERAGEX (
WINDOW (
-2, REL,
0, REL,
_Months_,
ORDERBY ( 'Date'[Month Number Of Year], ASC )
),
[Sum Sales Amount]
)
I've made three main changes.
Many thanks for your help!
You're using ALLSELECTED inside of an iterator. Understanding what happens in that situation is highly complex so I recommend avoiding that if possible (by defining it as a variable outside of the iterator, for example).
Ignoring that, I think the core problem here is that the table you define inside WINDOW doesn't have any corresponding outer value to define the current row in your first example. That is, your WINDOW table is trying to sort on [Month Number Of Year] but that column doesn't exist in your table.
I've not tested this but it might work better:
SMA 3 Months =
VAR _Months_ =
SUMMARIZE (
ALLSELECTED ( 'Date' ),
'Date'[Month Number Of Year],
'Date'[English Month Name]
)
RETURN
AVERAGEX (
WINDOW (
-2, REL,
0, REL,
_Months_,
ORDERBY ( 'Date'[Month Number Of Year], ASC )
),
[Sum Sales Amount]
)
I've made three main changes.