Moving Average with Small Multiples - Value Same Across Small Multiples?

[willchamp]

I am creating a chart that compares an average value against a 1-hour moving average value. I have defined my moving average measure as:

1-Hour Rolling Average = 
VAR _end = 
    max('Query Table'[start_time])

RETURN
    CALCULATE(AVERAGE('Query Table'[average_queries]),
        FILTER(
            ALLSELECTED('Query Table'),
            'Query Table'[start_time] <= _end 
                && DATEDIFF('Query Table'[starttime], _end, MINUTE) <= 60
        ))

When I create my chart with my start_time, my average_queries field, and my 1-Hour Moving Average measure, though, I get the same moving average line for all small multiples:

I know that I need to do something to adjust the context the measure is operating in, but I'm stuck! Any tips would be appreciated.

[Anonymous · Accepted solution]

Hi @willchamp ,

I think you can try to use ALLEXCEPT() function in your measure. You need to add the column in small multiple in it.

1-Hour Rolling Average =
VAR _end =
    MAX ( 'Query Table'[start_time] )
RETURN
    CALCULATE (
        AVERAGE ( 'Query Table'[average_queries] ),
        FILTER (
            ALLEXCEPT ( 'Query Table', 'Query Table'[Column in Small Multiple] ),
            'Query Table'[start_time] <= _end
                && DATEDIFF ( 'Query Table'[starttime], _end, MINUTE ) <= 60
        )
    )

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@willchamp , Use a datetime table join with datetime of your table. That will allow you not to use ALLSELECTED('Query Table') and other contexts may pass

Datetime = distinct('Query Table'[starttime])

1-Hour Rolling Average =
VAR _end =
max('DateTime'[starttime])

RETURN
CALCULATE(AVERAGE('Query Table'[average_queries]),
FILTER(
ALLSELECTED('DateTime'),
'DateTime'[starttime] <= _end
&& DATEDIFF('DateTime'[starttime], _end, MINUTE) <= 60
))

Or consider window function