How SUMMARIZE works and does it create a row context?

[AhmadBakr]

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]

EVALUATE

VAR T1 =

SUMMARIZE(

    Employees,

    Employees[emp ID],

    Employees[BU],

    "MaxD", MAX( Employees[Days] )

)

VAR T2 =

SUMMARIZE(

    T1,

    Employees[BU],

    "AvD", AVERAGEX( T1, [MaxD] )

)

VAR T3 =

ADDCOLUMNS(

    VALUES( Employees[BU] ),     // SUMMARIZE(Employees, Employees[BU]) also works here in place of VALUES()

    "AvD",

    AVERAGEX(

        FILTER(

            T1,

            Employees[BU] = EARLIER( Employees[BU] )

        ),

        [MaxD]

    )

)

VAR T4 =

GROUPBY(

    T1,

    Employees[BU],

    "AvD", AVERAGEX( CURRENTGROUP(), [MaxD] )

)

RETURN T4

[Praful_Potphode]

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 T2

Please give kudos or mark it as solution once confirmed.

Thanks and regards,
Praful

[AhmadBakr]

In fact looks like SUMMARIZE operation is very complex. The article shared by @lbendlin above is very useful and reminded me of similar articles published by SQLBI on SUMMARIZE, and many videos on the toutube channel as well.

[lbendlin · Accepted solution]

Buckle up, this will be a wild ride.

All the secrets of SUMMARIZE - SQLBI

[AhmadBakr]

I also found this, as a legit better option: SUMMARIZECOLUMNS

SQLBI again.

https://youtu.be/yf0T6H1gP7I?si=oAvAGaOKE3j_jYl_

[AhmadBakr]

Thank you very much. This is quite deep and thourough