For each row with propagated filter context get AVG for group and calculate diff

[Anonymous]

Hello guys,

I have issue that I cannot solve 😞

I have following schema

with tables and values

DATE

QUEUE

queueId	AppId
1	1
2	1
3	2
4	2
5	3
6	3
7	4
8	4
9	5
10	5

App

AppID	AppName
1	App1
2	App2
3	App3
4	App4
5	App5

RESULTS

result	dateId	queueId
1	1	1
3	2	2
2	3	3
6	4	4
10	5	5
20	1	6
6	2	7
4	3	8
5	4	9
9	5	10

and my goal is simple I need 2 DAX measures only

1) that will ALWAYS calculate AVG for all apps, so if I will split the table by queueID or more columns it will hold always(!) avg value and not avg of row

so NOT THIS

AppName	queueId	Average Result
App1	1	1
App1	2	3
App2	3	2
App2	4	6
App3	5	10
App3	6	20
App4	7	6
App4	8	4
App5	9	5
App5	10	9

BUT THIS (just ignore all additional fields that will splitt Average Result ?

AppName	queueId	Average Result
App1	1	2
App1	2	2
App2	3	4
App2	4	4
App3	5	15
App3	6	15
App4	7	5
App4	8	5
App5	9	7
App5	10	7

is it even possible?

2) DAX I would like to get additional DAX measure that will compare current value with AVG of group

AppName	queueId	Average Result	Result	Diff
App1	1	2	1	1
App1	2	2	3	-1
App2	3	4	2	2
App2	4	4	6	-2
App3	5	15	10	5
App3	6	15	20	-5
App4	7	5	6	-1
App4	8	5	4	1
App5	9	7	5	2
App5	10	7	9	-2

Is it possible or not because of data model ? (unfortunatelly I cannot change it )

[Anonymous]

Hello @Anonymous ,
thanks for reply, but unfortunatelly it seems that DAX AVG causing multiplication 😞

otherwise but it seems good, can you help me to debug it ?

[Anonymous]

Hi @Anonymous ,

You can try formula like below to create measures:

AvgResultAllApps = 
CALCULATE(
    AVERAGE('results'[result]),
    ALLEXCEPT('queue', 'queue'[AppId])
)

Diff = 
SUMX(
    'results',
    'results'[result] - [AvgResultAllApps]
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.