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.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
7 | |
6 |