Filter table by current row value then take either average or current value

[jacinto]

I spent quite a lot on this but still I am struggling. Here is what I want to achieve, I am happy with either measure or column that gives me accurate result. Filter List table by current Item then if CountryDS="GLO"  avg will be current value, otherwise avg is average of filtered table.  For example when we filter List table by Item=Carbs, there "GLO" in the CoutnryDS thus avg will be 2 for all Carbs. However for Fatty acid since there is no GLO, average is average of the values. I get an error stating temp can't be used and it needs base table.  Any suggestions is greatily appreciated!

avg =

VAR temp =

SUMMARIZE (

List,

List[Item],

List[CountryDs],

"avgFP", AVERAGE ( List[value] )

)

VAR GloValueCC =

CALCULATE (

AVERAGE ( temp[value] ),

FILTER ( temp, temp[CountryDs = "GLO" )

)

VAR country_avg =

AVERAGE ( temp[value)] )

RETURN

IF (

ISBLANK ( COUNTROWS ( CALCULATETABLE ( temp, temp[CountryS] = "GLO" ) ) ),

country_avg,

GloValueCC

)

Item	CountryDs	Value	avg
Fatty acid	RoW	1.2	2.6
Fatty acid	RER	3.1	2.6
Fatty acid	RoW	3.7	2.6
Fatty acid	RER	4	2.6
Fatty acid	RoW	1	2.6
Carbs	RER	5	2
Carbs	RoW	1.5	2
Carbs	GLO	2	2
Carbs	RER	9	2

[jacinto]

Thank you very much! Sorry for late reply, it works like a charm!

[johnt75]

If you know that there is at most one value of GLO for each item then you can try

avg =
var GLOValue = CALCULATETABLE(SELECTCOLUMNS( 'List', "@val", 'List'[Value]), 
   ALLEXCEPT('List', 'List'[Item])
)
return IF( NOT( ISBLANK( GLOValue)), GLOValue,
   CALCULATE( AVERAGE('List'[Value]), ALLEXCEPT('List'[Item]))
)

[amitchandak · Accepted solution]

@jacinto , Try a new measure like

var _1 = calculate( Average(Table[Value]), filter( allselected(Table) , Table[Item] = max(Table[Item])))
var _2 = calculate( Average(Table[Value]), filter( allselected(Table) , Table[Item] = max(Table[Item]) && Table[CountryS] = "GLO" ))
return
if(isblank(_2), _1, _2)