Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I'm trying to create a column with an average for each Name, but the formula should only factor in each unique Code instance one time. Here is the data below with expected output on the right.
For name A1, the average is 60, because we are adding 100, 50, and 30 (one instance each of code xx, yy, and zz regardless of how many times the codes appear) and dividing by the 3 unique codes, and for B1 the average is 20 for the same reason ((10 + 25 +25) / 3).
OUTPUT | |||
Name | Code | Value | Average |
A1 | xx | 100 | 60 |
A1 | xx | 100 | 60 |
A1 | xx | 100 | 60 |
A1 | xx | 100 | 60 |
A1 | yy | 50 | 60 |
A1 | yy | 50 | 60 |
A1 | yy | 50 | 60 |
A1 | zz | 30 | 60 |
B1 | aa | 10 | 20 |
B1 | aa | 10 | 20 |
B1 | bb | 25 | 20 |
B1 | cc | 25 | 20 |
B1 | cc | 25 | 20 |
Appreciate the help!
You can create a measure like
Distinct average =
AVERAGEX(
SUMMARIZE( 'Table', 'Table'[Code], 'Table'[Value] ),
'Table'[Value]
)
Unfortunately this formula does not work, that generates an average for the whole table, it does not generate an average within each Name
It should do if you put it in a visual with the Name column
User | Count |
---|---|
86 | |
82 | |
68 | |
64 | |
55 |
User | Count |
---|---|
123 | |
100 | |
90 | |
83 | |
66 |