Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sokatenaj
Helper III
Helper III

Really Stuck with Some Calculations

Hi Folks, 

 

I have been building in Power BI like a boss over the last 6 months thanks to everyone's help here. However, I am stuck between a rock and a hard place right now. 

 

Here is a picture of my relationships: Capture.PNG

 

What I need to do is take the "Variable Target Amount" (which is in various currency types - GBP, EUR, USD, etc) from the Sales Bonus & Rating table and multiply it against the "Exchange Rate" field on the FX Rates table that contains the value to convert. 
"From Currency" on the FX Rate table would be the "Input Currency Code" on the Sales Bonus and Rating table. I need to keep the relationship from Sales Bonus & Rating to the Active table because I need the Employee Number as the key as I need to roll in some other fields from that table. However, I tried doing a calculation of this: 

 

Adj. Var Rate (USD) = SUMX(Sales Bonus & Rating, Sales Bonus & Rating[Variable Target Amount]*Related('FX Rates'[Exchange Rate]))

 

It didn't work. It says it doesn't know the relationship. I can't create another relationship between the Sales Bonus & Rating table and FX Rates because it will cause ambiguity with the Active table. How do I get this to calculate? The only reason I have to do this is because this is a population of sales employees that are getting commissions. 

 

Additionally, I will then need to take that converted rate and add it to their Annual Salary field on the Active table (hence the employee number need). Once I do that, I need to add those two values together (annual salary + converted rate).

 

I'm so lost. Then again, I've been at this for 13 hours because I'm on a severe time crunch. Many many thanks for any help!!!

1 ACCEPTED SOLUTION
ThisIsFalse
Resolver I
Resolver I

A am new to the DAX Languge and Power BI in gerneral mostly messing around with the power m Query/data shaping side, but I suspect the reason it fails is that the related goes from one to many then many to one. So the way I see it if you go from the one employee and his target amount to the next table using the employee number you now have several employee id's possibly related to the currency you want to convert. I think this is where the related function gets confused. Isn't there a way to use the currency code in the sales bonus table to directly compute the exchange rates? In power M I would create a fucntion that simply uses the currency codes in each row in the sales table to select the row in the fx rates table.  I would be suprised if there is not an analog in DAX.   I hope you figure it out.

View solution in original post

2 REPLIES 2
ThisIsFalse
Resolver I
Resolver I

A am new to the DAX Languge and Power BI in gerneral mostly messing around with the power m Query/data shaping side, but I suspect the reason it fails is that the related goes from one to many then many to one. So the way I see it if you go from the one employee and his target amount to the next table using the employee number you now have several employee id's possibly related to the currency you want to convert. I think this is where the related function gets confused. Isn't there a way to use the currency code in the sales bonus table to directly compute the exchange rates? In power M I would create a fucntion that simply uses the currency codes in each row in the sales table to select the row in the fx rates table.  I would be suprised if there is not an analog in DAX.   I hope you figure it out.

Thanks. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.