cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sokatenaj Member
Member

Missing Values

Hi Folks,

 

I have the following data: 

Capture.PNG

 

 

 

 

 

 

 

 

 

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!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
sokatenaj Member
Member

Re: Missing Values

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 Smiley Happy 

 

Thanks so much. 

5 REPLIES 5
Super User
Super User

Re: Missing Values

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])

 

 





Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.




 




sokatenaj Member
Member

Re: Missing Values

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

Super User
Super User

Re: Missing Values

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





Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.




 




v-huizhn-msft Super Contributor
Super Contributor

Re: Missing Values

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

Highlighted
sokatenaj Member
Member

Re: Missing Values

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 Smiley Happy 

 

Thanks so much.