Forum Discussion
How SUMMARIZE works and does it create a row context?
I have this employee sample dataset, fact table is Employees:
| emp ID | BU | Days |
| 1 | 1 | 0 |
| 1 | 1 | 30 |
| 1 | 1 | 60 |
| 2 | 1 | 0 |
| 2 | 1 | 30 |
| 3 | 1 | 0 |
| 3 | 1 | 30 |
| 3 | 1 | 60 |
| 3 | 1 | 90 |
| 4 | 2 | 10 |
| 4 | 2 | 20 |
| 5 | 2 | 10 |
| 5 | 2 | 20 |
| 5 | 2 | 30 |
I want to get average days per business unit (BU), but since each employee has repeated records, I want to use the record with maximum number of days to express working days of each employee. I used the below in DAX query view:
Note:
T1 is intermediate to group for max days.
T2 is the target, but returned wrong results: average over all T1, not average grouping over BU!
T3 and T4 returned correct results.
Why did T2 fail? Why it did not create [BU] row context and group on it like T1 did with [emp ID]
5 Replies
Thank you very much. This is quite deep and thourough
I also found this, as a legit better option: SUMMARIZECOLUMNS
SQLBI again.
Hi AhmadBakr ,
AVERAGEX(T1, [MaxD]) is evaluated once for the entire T1 table — not per BU.Even though SUMMARIZE lists Employees[BU], it doesn’t automatically create row context for AVERAGEX to iterate per BU.So T2 ends up calculating the overall average of all MaxD values, and repeats that same value for every BU
VAR T1 = SUMMARIZE( Employees, Employees[emp ID], Employees[BU], "MaxD", MAX(Employees[Days]) ) VAR T2 = ADDCOLUMNS( SUMMARIZE(T1, Employees[BU]), "AvD", CALCULATE(AVERAGEX(T1, [MaxD])) ) RETURN T2Please give kudos or mark it as solution once confirmed.
Thanks and regards,
Praful
