it is normal using a left join, it is just because that field is present on the second table, so depending on the lines present on the first table, you're maybe not loading all the rows of table2
if you do a right join, I'm sure you'll find 497k.
Maybe should re-think about the join you need on this case ?
I think everyone have already mentioned that there seems to be many to many join here which might be causing the number of rows to increase... which cause your measure to multiply. Look at a very simple example here
When you join these two tables, you will get this
Dim1, Value1, Value2
A, 10, 20
A, 20, 20
A, 30, 20
A, 10, 30
A, 20, 30
A, 30, 30
Now you can clearly see that if you do Sum(Value1), it will give you 120 instead of 60 you expected. and Sum(Value2) will be 150 instead of 50.
To find if this is true or not... run the two files separately and create a straight table with key fields you are joining on as dimensions
If you are seeing rows where the expression is giving you more than 1, then you see your problem right away.
Thank you sunny bhai..
I am attaching he qvw.. can you please check and tell me where i am doing wrong..
test (4).qvw 9.6 MB
See how many times your key field is repeating
If the goal is to just fix the sum, you can try this
=Sum(Aggr(Sum(DISTINCT ADJ_CONS_YTD_LY), Plant, Country, Comment_New, BU, Entitlement, YearMonth))
test (4) (1).qvw 9.7 MB