I replaced a Name in a column if another had specific text, now the New Names sums are wrong

[Anonymous]

I have data coming from 2 different sources, one is MSBI and the other is Excel.  The MSBI is revenue and the Excel sheet is the sales person's monthly goals.  Due to a sales person leaving I was asked to replace his name with "Temp" when another column had a client's name that he serviced.

This is what I wrote:

=if('Table'[ClientName]="ABC","Temp,if('Table'[ClientName]="DEF","Temp",'Table'[SalesPersonName])

Works perfectly until I need to display the revenue and goals in the same table using New Sales Name column.  Then what happens is "Temp" duplicates each sales person's montly goals.  

1/31/17 SalesGuyA $123

1/31/17 SalesGuyTemp $123

1/31/17 SalesGuyB $456

1/31/17 SalesGuyTemp $456

This doesn't happen when I use the Sales Person Name column that's original to the dataset.

[vanessafvg]

@Anonymous  

i am not quite getting what you saying here, you have data from 2 sources, in those 2 sources you are changing the name of the ex employee to the temp, so the names so the names will match exactly?

how is the the sales data joined to the targets, when you combine this data?

[Anonymous]

I've put "Temp" in both datasets to match them and they do connect, but the totals are either wrong for the revenue or wrong for the goals depending on which Name List I use or which table I pull the Month Year date from.

[v-sihou-msft · Accepted solution]

@Anonymous

This is expected behavior. Since you replace both "ABC" and "DEF" into "TEMP", when draging the person name field into a table, it will aggregate on same name by default.

If this is only for report rendering, you can just click on "Goal" field and select "do not summarize". But if you need to do some calculation on person level, all left people's data will aggregate on "TEMP". You have to give a different temp name for each left person.

Regards,