Forum Discussion
How to remove context from a temporary contextually-calculated table-variable ?
Within the definition of the column you're adding using ADDCOLUMNS you can manipulate the filter context in any way you want, or you can manipulate the filter context in a CALCULATETABLE as in the above example where the date is being manipulated.
The general method is to use ADDCOLUMNS .. SUMMARIZE to build the temporary table with an additional column containing the value you want to average, then use AVERAGEX over that table.
For example, if you wanted the average sales by brand over a given period you could use something like
Average by brand over time =
VAR MinDate =
MIN ( 'Date'[Date] )
VAR MaxDate =
MAX ( 'Date'[Date] )
VAR SummaryTable =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE ( Sales, 'Date'[Year month], 'Product'[Brand] ),
"@sales amount", CALCULATE ( SUM ( 'Sales'[Amount] ) )
),
'Date'[Date] >= MinDate
&& 'Date'[Date] <= MaxDate
)
VAR Result =
AVERAGEX ( SummaryTable, [@sales amount] )
RETURN
Result
Thank you for trying to help me.
I still get the same result as my underlying measure, as my context is still inherited from the underlying. Is there no way to extand the time context to the adjacent entries ?
Within the definition of the column you're adding using ADDCOLUMNS you can manipulate the filter context in any way you want, or you can manipulate the filter context in a CALCULATETABLE as in the above example where the date is being manipulated.
Thank you very much that was really helpfull. I didn't know you could redefine the context like that.