summarize and allexcept

[Anonymous]

Hello, 

My question is how can i use allexcept function to refer to a table which i have calculated (i.e. summarize)?

Here is the calculated table:

Lookup table = SUMMARIZE(FILTER(Table1,Table1[ConditionType]="PREPAID"), Table2[ObjID],
Table2[Cities],
Table2[Countries],
"SUM",
SUM(Table1[Amount/Year])

)

My goal is to add another column after "SUM" to get the average per cities and per countries. I was thinking of using average function and allexcept. Is there a way for allexcept to refer to Lookup table? or it will refer to the table2? my idea is if i can do this:

Lookup table = SUMMARIZE(FILTER(Table1,Table1[ConditionType]="PREPAID"), Table2[ObjID],
Table2[Cities],
Table2[Countries],
"SUM",
SUM(Table1[Amount/Year]),

"AVE Cities",

CALCULATE(AVERAGE(Table1[Amount/Year]),ALLEXCEPT(Lookuptable, Lookuptable[Cities]))

)

But it appears i can't do this since it still inside the calculated formula of the table. I tried adding calculated column, but somehow it is not working. 

[az38]

Hi @Anonymous 

try ADDCOLUMNS. like this

Lookup table = 
ADDCOLUMNS(
SUMMARIZE(FILTER(Table1,Table1[ConditionType]="PREPAID"), Table2[ObjID],
Table2[Cities],
Table2[Countries],
"SUM",
SUM(Table1[Amount/Year])),
"AVE Cities",
CALCULATE(AVERAGE(Table1[Amount/Year]),ALLEXCEPT(Lookuptable, Lookuptable[Cities]))
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

It seems i still can not access the "lookup table" for the allexcept since it is within the lookup table formula even if i add addcolumns