Average based on criteria on same column

[Anonymous]

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.67%
Q	14	84.50%

What i want to achieve is basically  = Completion / Count of No that has the same value. Refer to Average % Completion column below:

Names	#No	% Completion	Average % Completion
A	1	97.50%	97.00%
D	4	88.89%	88.89%
E	5	73.38%	86.69%
F	5	100.00%	86.69%
G	6	100.00%	100.00%
N	13	82.08%	82.92%
O	13	83.75%	82.92%
P	14	86.67%	85.59%
Q	14	84.50%	85.59%

% Completion is a measure = Average(Completion) based on Names.
So basically now what is want is Average of ( Average(completion) based on names) based on #No

And my final table will be WITHOUT #No Column

Names	% Completion	Average % Completion
A	97.50%	97.00%
D	88.89%	88.89%
E	73.38%	86.69%
F	100.00%	86.69%
G	100.00%	100.00%
N	82.08%	82.92%
O	83.75%	82.92%
P	86.67%	85.59%
Q	84.50%	85.59%

I have tried  some of calculation but most of it doesnt work after i get rid of #No column

Thanks.

[Greg_Deckler]

Try:

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

See attached.

[Anonymous]

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? 

[HotChilli]

Removing '#No' from your visualisations changes context so I think you need something like this

Measure  = 
CALCULATE (
    AVERAGE(Table[% Completion]), 
    FILTER (
        ALL ( Table ), 
        Table[#No] = MAX ( Table[#No] )
    )
)