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 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.

 

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.

2 Replies

  • 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

     

     

  • 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.