How to remove context from a temporary contextually-calculated table-variable ?

[StSupQ]

Hello all,

I am currently working on a moving average measure.
The underlying data i want to smooth that way is already a measure. I am already displaying it on a visual as a fonction of a [Time] column, and i want to display the smoothed curve on the same visual. My visual will be filtered on another [Perimeter] column, that is why the underlying data is already a measure.

I have created a temporary table with the ADDCOLUMNS function as follows: 

VAR tempTable=

ADDCOLUMNS(

    FILTER(

        'Table1',

        [Time]>=windowStart && [Time]<= windowEnd && [Perimeter]=perim

    ),

    "Measure Value",

    [Measure]

)

I want to return the average value of the column [Measure Value] from that table.
I can't just use AVERAGEX as it keeps the underlying filter context, and I can't use ALL, ALLSELECTED or REMOVEFILTERS as it will display as an error that i must reference a table and not use the expression of one. Since this temporary table is contextually created, I can't just use a fixed table which would be easy. I would love to just use AVERAGE(tempTable[MeasureValue]) but it seems like you can't reference columns from table variables that way.

Any help of any kind would be greatly appreciated.
Thank you, 
StSupQ

[johnt75]

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] )

[StSupQ]

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.

[johnt75]

What are you trying to get the average over - over time, over products ?

[StSupQ]

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 solution

[johnt75]

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

[StSupQ]

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 ?

[johnt75 · Accepted solution]

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.

[StSupQ]

Thank you very much that was really helpfull. I didn't know you could redefine the context like that.