There are several issues with your calculations. I do not think we have a solid understanding of ATTR() so lets start there.
Attribute checks if the MIN of a field is the same as the MAX of a field. If it is then it will return that Field. If not it will return an asterisk. You have multiple scores for each person so you get an asterisk for each person. This equates to a NULL when used in a calculated field and you're unable to get any values for the 'No achieved target' and by result the 'hit rate'.
Ok so with that established lets look at the 'No Achieved Target' Field
if ATTR([Score]) > ATTR([Person Only].[Target Standard]) then 1 else 0 end
I will change the ATTR(Score) to MAX in this case. if MAX([Score]) > ATTR([Person Only].[Target Standard]) then 1 else 0 end
It will look through all of your scores and choose the maximum value and then compare that to the target standard for the person only database. This results in only a 0 or a 1. This is not going through each score like you imagine. When you blend you are comparing already aggregated values.
Finally Hit Rate is using
an aggregate divided by a row level field (non-aggregate)
If you instead use [No. of Achieve Taraget]/SUM([Number of Records])
You will be aggregating the number of records field at the same time and the calculation is valid, but probably not accurate for what you want.
It looks like you want to compare every instance of your score to the target standard in the other database. Find out how many were greater and then divide that by the total number of scores. With blending I do not think this is possible. Again when you blend you take the aggregate of both sides and then compare in your case you are trying to aggregate the comparison and it doesnt work that way.
Where are these datasources stored if they are in the same database you could do this with a join instead.
Thanks for your explanation. You are right, I would like to compare every instance between two data sources. In my real cases, I'm trying the compare the datetime fields,
[No. of Achievement] (Aggregated)
if DATEDIFF('minute',ATTR([Expected Date Time]),ATTR([Other Data Source].[Actual Time])) < 0
//AND some condition checking
[% Achievement Rate] (Can't use SUM to Aggregate again)
SUM([No. of Achievement])/SUM(Fulfilled Condition Records)
Because the data sources come from different databases (Oracle, mySQL) and Excel, I can't join the data directly. In my case, should I use the data integration tools to migrate the records into single database before continue the Tableau report development?
I'd be putting them in the same database if possible. Then life will be
much easier. As you can join and run similar calculated fields to make the
pain go away.