DAX Help, store value in variable unfiltered but filtered by slicer

[timwilson]

Hi,

I'm trying to recreate a contact centre view, I require staff required and staff on phone by interval.

Calls that have been answered attract handling time, and when I try and store that as a variable and multiply that by all calls (not just answered), it only will multiply it by the calls answered, I assume that's because it's matching rows for the handling time measure.

How do I store the handling time measure in a variable that can be used regardless if there is a matching row for the call counts?

The workload (FTE) measure should return a different result than Onphone(FTE). 

Workbook : https://drive.google.com/open?id=0B7_6Zpn28a-LTUk1VmxwYXBLajA

[vanessafvg]

is it always the same amount?  ive looked at your model but can you direct me to the exact measure you are trying to accomplish this?

[timwilson]

No the problem is that it's showing as the same amount in the model.

The calculation is ((calls*handletime)/intervallengthseconds (900) in this case).

It should look like this:

Where wkload for 8:30 interval is (32*544.6)/900

Interval	InboundQueuedCount	AHT	WkLoad	OnPhone
3/05/2017 8:30	32	544.6	19.4	10.9
3/05/2017 8:45	17	620.2	11.7	6.2
3/05/2017 9:00	25	641.9	17.8	3.6
3/05/2017 9:15	35	509.4	19.8	9.1
3/05/2017 9:30	23	440.9	11.3	4.9
3/05/2017 9:45	19	553.7	11.7	6.8

In the model I get the lower figure which leads me to believe it's only calculating rows where the AHT measure components are populated, if blank it seems to ignore the calls.

[v-sihou-msft · Accepted solution]

@timwilson

You input incorrect AHT expression in Workload(FTE) measure.

Your measue is:

WorkLoad(FTE) =
VAR AHT =
    DIVIDE (
        (
            SUM ( Query1[ActiveDuration] ) + SUM ( Query1[HoldDuration] )
                + SUM ( Query1[ConsultDuration] )
                + SUM ( Query1[ConfDuration] )
                + SUM ( Query1[WrapDuration] )
        )
            / 1000,
        SUM ( Query1[InboundQueuedCount] ),
        0
    )
VAR QueueCalls =
    SUM ( Query1[InboundQueuedCount] )
RETURN
    DIVIDE ( ( QueueCalls * AHT ), 900, 0 )

But your AHT measure is:

AHT =
DIVIDE (
    (
        SUM ( Query1[ActiveDuration] ) + SUM ( Query1[HoldDuration] )
            + SUM ( Query1[ConsultDuration] )
            + SUM ( Query1[ConfDuration] )
            + SUM ( Query1[WrapDuration] )
    )
        / 1000,
    CALCULATE ( SUM ( Query1[InboundQueuedCount] ), Query1[CallActionId] = 8 ),
    0
)

Just replace AHT variable with correct formula, it can return correct result.

Regards,

[timwilson]

Oh wow, that is really silly of me.

Thank you!