Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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?
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] ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |