Missing Values

[sokatenaj]

Hi Folks,

I have the following data: 

I have the following measure that represents "Annual Salary (USD)": 

            Annual Salary (USD) = SUMX(Active, Active[Annual Salary (Local Currency)]*Related('FX Rates'[Exchange Rate]))

I have a foreign exchange table has the rates to calculate from our different currencies to USD so we can budget in US Dollars. However, there are some lines that just don't calculate and I have NO idea why. If you notice, the 5th and 6th lines are not calculating to USD. Even though the previous lines do and subsequent lines do. 

What the heck could be the issue? I have about 80 employees out of 7000 that this is happening to from the "Annual Salary (Local Currency)" field, but the data looks the same. When I look at the raw data from Oracle, it looks fine. Any idea why this is happening? I'm so stumped! 

Please and thank you!

[parry2k]

did you added this new calculation as a measure or column.

Can you try following, add as a new column:

Annual Salary (USD) Test = Active[Annual Salary (Local Currency)]*Related('FX Rates'[Exchange Rate])

[sokatenaj]

It's a measure used as a field in a table. 

[v-huizhn-msft]

Hi @sokatenaj,

Please create a calculated column using the formula below in Table 'Active', and check if the value is blank for 5th and 6th rows.

Exchange Rate=Related('FX Rates'[Exchange Rate])

Please respond to me if you have any other issue.

Best Regards,
Angelia

[sokatenaj · Accepted solution]

I ended up figuring out what the issue was. I had a table with a relationship that was causing missing values because of the bidirectional nature of the relationship and the type. I removed it and just did it as a VLOOKUP and all is well 🙂 

Thanks so much. 

[parry2k]

Did you tried adding a column as suggested in my previous reply?