Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Average based on criteria on same column

Hi I have a data that looks like this Names #No % Completion A 1 97.50% D 4 88.89% E 5 73.38% F 5 100.00% G 6 100.00% N 13 82.08% O 13 83.75% P 14 86...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
7 years ago

Try:

 

Measure = 
VAR __no = MAX([#No])
VAR __table = FILTER(ALL(Table1),[#No] = __no)
RETURN
AVERAGEX(__table,[% Completion])

See attached.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Greg_Deckler  & HotChilli ,

Weirdly i used both of your formula on my dummy table it works, but when i used it on my real data it doesnt work. FYI i filter by year (single select) is it somehow one of the reason why it doesnt work?