Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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 Tabl...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    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's avatar
amitchandak
Super User
2 years ago

Anonymous , 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