Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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