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.
You can use AVERAGEX over the temporary table,
Avg of measure =
VAR tempTable =
ADDCOLUMNS (
FILTER (
'Table1',
[Time] >= windowStart
&& [Time] <= windowEnd
&& [Perimeter] = perim
),
"Measure Value", [Measure]
)
RETURN
AVERAGEX ( tempTable, [Measure Value] )
Hi,
Thank you for the proposition.
However, when I do so, it keeps the filter context of the base table, which filters this calculated table to only the current date. So it will do the average of only one value, which returns the same curve as the underlying measure.
What I want to do is to remove the filter context on that calculated table (while keeping it only between the windowStart and windowEnd time variables) and only then calculate the average.
What are you trying to get the average over - over time, over products ?
My final result is the curve of the moving average over time. As to what that is, each data point of the moving average would be the average of the 3 preceiding data points, the current one and the next 3 from the underlying measure. (See this wikipedia article for further detail: https://en.wikipedia.org/wiki/Moving_average)
I have managed to create that moving average as a calculated column with another column as underlying. What I am trying to do here is have it as either a column or a measure based on an underlying that is a measure. I want that this way so that it can be dynamically filtered using an important range of possible filters.
The way that I wanted to process was by having my underlying data copied and filtered in a temporary table, to which I would add the in-context measure and calculate the average of the column of the whole table.
I would love it if you had any idea of a possible solutionThe 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