Calculate average of column by date and one more column

[odzelashvili1]

Hi PBI community,

my table looks like this:

and i want to create a measure, which calculates average [waiting_time] of unique [visit_key] by:

1. [queue_key] = 1 or 14

2. [time_key] = min of the column.

In this little example my measure must calculate [waiting_time] for: 

[visit_key] = 88279 [waiting_time] = 125

[visit_key] = 88283 [waiting_time] = 0

[visit_key] = 88290 [waiting_time] = 0

[visit_key] = 88307 [waiting_time] = 0

[visit_key] = 88022 [waiting_time] = 699

which is 164.8

Any suggestions?

[Jos_Woolley · Accepted solution]

Hi,

Average Waiting Time =
VAR MyTable =
    SUMMARIZE (
        'Table',
        'Table'[visit_key],
        "Min Waiting Time", CALCULATE ( MIN ( 'Table'[waiting_time] ), 'Table'[queue_key] IN { 1, 14 } )
    )
RETURN
    AVERAGEX ( MyTable, [Min Waiting Time] )

Regards

[Jos_Woolley · Accepted solution]

Not sure I've fully grasped the nature of the source data, so you'll need to check this:

Waiting Time For Min Key =
VAR MyTable =
    SUMMARIZE (
        'NewTable',
        NewTable[visit_key],
        "Min Time Key",
            LOOKUPVALUE (
                NewTable[waiting_time],
                NewTable[time_key], CALCULATE ( MIN ( 'NewTable'[time_key] ), 'NewTable'[queue_key] IN { 1, 14 } )
            )
    )
RETURN
    AVERAGEX ( MyTable, [Min Time Key] )

Regards

[Jos_Woolley]

Ok, but first can you clarify what happens if there is more than one occurrence of min [time_key] for a given [visit_key]? Which [waiting_time] do we take in that situation? For example, in the below is 50 or 136 to be returned?

time_key	visit_key	visit_outcome_key	queue_key	waiting_time
36420	88316	5	1	136
36420	88316	5	133	50
36660	88316	5	133	78
38340	88316	5	14	0
38340	88316	5	14	30
38400	88316	5	23	0
38400	88316	3	23	58

Regards

[odzelashvili1]

Earlier [time_key] cannot be more than one row (with [queue_key] 1 or 14,  

this is possible, but not for min [time_key].

Ilia

[Jos_Woolley]

You're welcome!

Regards

[odzelashvili1]

Jos,

I have problem 🙂 

Using this code, I'm taking Min [waiting time] value in Mytable, which is incorrect, I need to take [waiting_time] by min [time_key].

e.i. 

In this case, your code will take 30, Instead of 136, can u modify code?

Thanks again 🙂

[Jos_Woolley · Accepted solution]

Hi,

Average Waiting Time =
VAR MyTable =
    SUMMARIZE (
        'Table',
        'Table'[visit_key],
        "Min Waiting Time", CALCULATE ( MIN ( 'Table'[waiting_time] ), 'Table'[queue_key] IN { 1, 14 } )
    )
RETURN
    AVERAGEX ( MyTable, [Min Waiting Time] )

Regards

[odzelashvili1]

Thank you 🙂