Forum Discussion
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.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.
3 Replies
- HotChilli
Community Champion
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] ) ) ) - Greg_Deckler
Try:
Measure = VAR __no = MAX([#No]) VAR __table = FILTER(ALL(Table1),[#No] = __no) RETURN AVERAGEX(__table,[% Completion])
See attached.
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?
