Sum a value with duplicate data

[Anonymous]

I have data that looks something like this:

UserID	Course Name	Other Value
1	Course 1	10
1	Course 1	4
1	Course 1	10
1	Course 2	10
1	Course 2	4
1	Course 2	10

The data is like this due to me joining two tables together - the "Course Name" and "Other Value" have nothing in common other than that they apply to the same user.

What I need to do is sum the "Other Value" so I get 24 for the user, regardless of the number of courses they have. I think I have to use some sort of aggregate function, I've played around with aggr but haven't had any luck.

Any help would be appreciated!!

[swuehl · Accepted solution]

I think it would be better to not join the tables, but keep your OtherValue fact values linked by UserID.

If you don't want to do it, maybe

=Sum(OtherValue) / Count(DISTINCT [Course Name])

[Anonymous]

Not sure what you want the end result to look like.  Are you looking for something like this?

UserID	Course Name	Other Value
1	Course 1	24
1	Course 2	24

It would essentially be an average.  If a user has a 3rd course with different total it could end up being not what you're expecting.

But to do this you could do.

aggr(nodistinct (Sum(OtherValue) / Count(DISTINCT [Course Name])),UserID)