Forum Discussion
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 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!
| 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 , 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)
3 Replies
- amitchandak
Super User
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) 
johnt75If 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])) )Thank you very much! Sorry for late reply, it works like a charm!
