Excel Formula Replication

[Hamdan1234]

I have got two tables in two sheets  and I have used Sumifs formula in excel to get a new column on the basis of both the tables.Tables are:

Month	Curr	Conversion Rate
Jan	AUD	0.769823
Jan	BRL	0.191741
Jan	CAD	0.785323
Jan	EUR	1.220601
Jan	GBP	1.363045
Jan	HKD	0.128978
Jan	INR	0.013683
Jan	SGD	0.756653
Jan	USD	1.000000

Month	Document Currency	Currency Conversion Outcome
Jan	USD	1
Jan	USD	1
Jan	USD	1
Jan	USD	1
Jan	USD	1
Jan	CAD	0.785323
Jan	USD	1
Jan	EUR	1.220601
Jan	USD	1

I have used this formulas =SUMIFS('Currency Key'!C:C,'Currency Key'!A:A,A2,'Currency Key'!B:B,B2) to get currency conversion out come. I want to get same column in power bi. I have to get this formula through these columns in power bi. How can it be done. It seems a bit complicated to me. Help would be appreciated.

Thanks

[v-angzheng-msft]

Hi, @Hamdan1234 

Can you please elaborate on what you are trying to achieve ? I'm not quite sure what output you're hoping to get. Could you draw a simple picture to show your expected visual so that i may work out with a workaround?

[manikumar34]

@Hamdan1234 ,
Create relationship on currency type and use RELATED or LOOKUPVALUE to get the value.

[Greg_Deckler]

@Hamdan1234 Try:

Currency Conversion Outcome (Column) =
  VAR __Month = [Month]
  VAR __DocumentCurrency = [Document Currency]
  VAR __ConversionRate = MAXX(FILTER('OtherTable',[Month]=__Month && [Curr]=__DocumentCurrency),[ConversionRate])
RETURN
  __ConversionRate