Forum Discussion

Hamdan1234's avatar
Hamdan1234
Helper III
4 years ago

Excel Formula Replication

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:

MonthCurrConversion Rate
JanAUD0.769823
JanBRL0.191741
JanCAD0.785323
JanEUR1.220601
JanGBP1.363045
JanHKD0.128978
JanINR0.013683
JanSGD0.756653
JanUSD1.000000

 

MonthDocument CurrencyCurrency Conversion Outcome
JanUSD1
JanUSD1
JanUSD1
JanUSD1
JanUSD1
JanCAD0.785323
JanUSD1
JanEUR1.220601
JanUSD1

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

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    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
  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    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?