Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 )
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 ?
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
7 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |