CODEWITHSUNDEEP
duplicatesaveragepowerpivotdax
C2BB
C2BB

Reputation: 88

Average Of Column Counting Duplicates Once - PowerPivot + DAX

I have a column in PowerPivot that I would like to get the average of. However I only want the rows included that are the only instance of a value or the first instance of a duplicate value in another column. Is this Possible with DAX?

Simply put I need the column average of unique rows, determining uniqueness from another column.

Upvotes: 1

Views: 4718

Answers (2)

mrP
mrP

Reputation: 178

My understanding of the OP's question looks something like this:

| id | age |
| -- | --- |
| 1  | 20  |
| 1  | 20  |
| 2  | 50  |
| 3  | 35  |
| 3  | 35  |

In this case, a summed average as suggested by aesthetic_a (40 + 50 + 70)/3 would not be appropriate.

However an averaged average ((40/2) + (50/1) + (35/2))/3 would be a solution to determine the distinct average grouped by id.
Measure:=AVERAGEX(VALUES(table[id]), CALCULATE(AVERAGE(table[age])))

Upvotes: 1

aesthetic_a
aesthetic_a

Reputation: 11

Probably to old to assist, but for those that stumble across:

You would need to create two measures. The first would sum whatever it is you are trying to average by the distinct values in the other column.

| id | squilla |
| 01 | 100 |
| 01 | 110 |
| 02 | 90 |
| 03 | 100 |
| 03 | 90 |

So id=1 has total squilla of 210, id=2 spend of 90, and id=3 spend of 190. The distinct average (where id is the identifier) is 163.333

To do this in powerpivot, first create a measure that sums the squilla by id: Measure1:=CALCULATE(SUM('yourTable'[squilla]),VALUES('yourTable'[id]))

And the second to average it across id: Measure2:=AVERAGEX(DISTINCT('yourTable'[id]),[Measure1])

Upvotes: 1

Related Questions