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.
A | B |
0.10 | 4945708906 |
0.86 | 4945708908 |
0.17 | 4945708909 |
0.29 | 6041671331 |
0.22 | 6041671331 |
1.51 | 6041671334 |
1.67 | 6041671334 |
0.22 | 6041671337 |
I would like to sum the values in column A, but if there are duplicates in column B, I want to average the values of the duplicates then include that average in the sum.
So instead of adding 0.29 and 0.22 as part of the sum of column A, first gather their average = .255 and use that in the total sum instead. Then do the same for 1.51 and 1.67, etc.
Solved! Go to Solution.
Should be easy enough with an intermediate summary table, something like:
summarize(yourtablename,[column b],"column a averaged",average([column a])
You can then sum up your new "column a averaged" column
Both solutions work, but what if I have additional columns that I want to SUM
Also this is all being done in a UNION(SELECTCOLUMNS( and if possible would like it achieved under "A"
SELECTCOLUMNS ( 'Table1', "A", 'Table1'[A], "D", 'Table1'[D], "E", 'Table1'[E], "C", 'Table1'[C]/60),
but maybe thats not feasible, or am setting this up wrong.
Nevermind, the Summarize expression works, I just have to keep adding to it and rebuild my table.
I like to use:
Average Measure = AVERAGEX(Sheet2,Sheet2[A])
Then
Sum Average Measure by B = sumx( DISTINCT(Sheet2[B]), [Average Measure])
Should be easy enough with an intermediate summary table, something like:
summarize(yourtablename,[column b],"column a averaged",average([column a])
You can then sum up your new "column a averaged" column
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 |
---|---|
114 | |
101 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |