Forum Discussion

jacinto's avatar
jacinto
Frequent Visitor
4 years ago
Solved

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

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 i...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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)

johnt75's avatar
johnt75
Super User
4 years ago

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]))
)