Averaging Not Working Correctly

[kliberty]

I am trying to get an average of a series of survey responses. I am connecting tables using a unique identifier. However one person has multiple identifiers and instead of looking at them seperately, BI is averaging the number of responses for both unique identifiers and assigning them as one average. 

EX.

One person: Karlee

Multiple unique identifiers: Karlee1 and Karlee2

Avg score of Karlee1 is 1

Avg score of Karlee2 is 2

PowerBI is printing an average score of 1.5 for both Karlee1 and Karlee2

I can't figure out how BI even recognizes them as joined, much less how it is deciding to average across the unique identifiers. Has anyone seen something like this before? 

[kliberty · Accepted solution]

A colleague of mine helped me reconnect all of my sources and it is working now. Thanks for your help! 

[v-jiascu-msft]

Hi @kliberty

Yeah, could you please post a sample? (a dummy sample is enough.)

Best Regards!

Dale

[kliberty]

Hi @v-jiascu-msft and @GilbertQ

I'm not sure how to do that. I can give show dummy data from the Excel source (the one that has the surveys), but the other source is from an oracle database, and I don't really know what it looks like outside of BI. 

How do you mean show you some samples? (Sorry I haven't ever reached out to the community before, so I don't really know what I'm doing).

[GilbertQ]

Hi there, if you had to drag in the unique identifier does it then give the correct average? It could be because it is doing the average based across all the data not taking into consideration the unique ID's?

[kliberty]

The unique identifier is the only connection between the two tables (one has all the surveys, one has personal information like name, dept., etc.) and different people are getting different scores, so I don't think it's averaging all the data. 

Putting the unique identifier in the table doesn't fix it though 😞 The table already sees "Karlee1" and "Karlee2" as two entries, but the surveys are just the same number for both. But different people (say Ethan or something) get a unique score, it's just the people who have two unique entries.

I just have no idea how it knows that those two are even related :(, much less how to force it to break them apart 

[GilbertQ]

Hi @kliberty

It would be great if you could create a sample dataset for us to have a look at?