Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
51 | |
27 | |
24 | |
13 | |
8 |
User | Count |
---|---|
74 | |
56 | |
47 | |
16 | |
12 |