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.

Anonymous's avatar
Anonymous
Not applicable
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.