Rolling average for last 24 hours.

[Daniel_Derbin]

Hello All,

I have a case to calculate average of response raet per hour as well as rolling average of response rate on data for last day starting at 12AM.

To deduct response rate i need first to calculate total calls coming into queue as well as calls answered and divide them into percentages.

Total calls =

CALCULATE(

    DISTINCTCOUNT(call_events[call_id]),

        FILTER(call_events, call_events[queue_key] <> BLANK()

    )

)

=========

answered calls =

    CALCULATE(

        DISTINCTCOUNT(call_events[call_id]),

            FILTER(call_events,call_events[dte_speak_start] <> BLANK()),

            FILTER(call_events,call_events[queue_key] <> BLANK())

    )

=====

Response rate =

        DIVIDE([answered calls],[Total calls])

Once i have measure for response rate i calculate average of it per hour:

Average response an hour =

AVERAGEX(

    VALUES(call_events[Hour]),

    [Response rate])

And up until this point all goes well so i can present averages on hourly basis

The issue occurs when i try to create measure for rolling average:

Rolling response =

AVERAGEX(

    FILTER(ALLSELECTED(call_events),

        call_events[Hour] <= MAX(call_events[Hour])),

        [Average response an hour])

The result in not what i expcected, firstly adding measure to any visual takes several minutes to load, and once it's there it returns with 100% rate

i've managed to apply such logic to another KPI where base value (Score) is available in table so no need to calculate it additionally:

Average score = AVERAGE(surveys[score])
========

Average hourly score =

AVERAGEX(

    VALUES(surveys[Hour]),  

    [Average score]

)

===========

Rolling score =

AVERAGEX(

    FILTER(ALLSELECTED(surveys),

    surveys[Hour]<=MAX(surveys[Hour])),

        [Average score]

)

And the result here is satysfying:

I would appreciate any help.

All best 🙂

[lbendlin]

Your title says "Rolling average for last 24 hours"  but your calculations say different (and they are missing the day filter).  Please clarify.,