Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

LOOKUPVALUE

Hi

 

I'm getting "NaN" from this formula, and i cant see why? Anything obviously wrong here?

 
Invoiced GGR (€) =
SUMX('Fact Workday Transactions',
'Fact Workday Transactions'[In Source Currency] /

LOOKUPVALUE('Exchange Rate'[Exchange Rate],
'Exchange Rate'[ExchangeRateDate],'Fact Workday Transactions'[Date],
'Exchange Rate'[CurrencyCode],'Fact Workday Transactions'[CUR]))
 
Type of columns are as follows:
 
'Fact Workday Transactions'[In Source Currency] = revenue in source currency (number)
'Exchange Rate'[Exchange Rate] = also a number
Dates are Dates...
'Exchange Rate'[CurrencyCode] = Text ABC, I followed an example online form Enterprise DNA and it was also Text
Fact Workday Transactions'[CUR]) = Text also
 
I cant see why this doesnt work? 
 
Relationship created between Calendar and Exchange Rate table (also calander to Fact table)
 
Any suggestions would be welcome.
Thanks in advance!!
Ben
Need Help
Show and Tell
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

     

    Try this:

    Invoiced GGR (€) =
VAR __Numerator =
    SUMX (
        'Fact Workday Transactions',
        'Fact Workday Transactions'[In Source Currency]
    )
VAR __Denominator =
    LOOKUPVALUE (
        'Exchange Rate'[Exchange Rate],
        'Exchange Rate'[ExchangeRateDate], 'Fact Workday Transactions'[Date],
        'Exchange Rate'[CurrencyCode], 'Fact Workday Transactions'[CUR]
    )
RETURN
    IF ( __Denominator <> 0, DIVIDE ( __Numerator, __Denominator ) )

     

    Or this:

    Invoiced GGR (€) =
VAR __Denominator =
    LOOKUPVALUE (
        'Exchange Rate'[Exchange Rate],
        'Exchange Rate'[ExchangeRateDate], 'Fact Workday Transactions'[Date],
        'Exchange Rate'[CurrencyCode], 'Fact Workday Transactions'[CUR]
    )
RETURN
    SUMX (
        'Fact Workday Transactions',
        IF (
            __Denominator <> 0,
            DIVIDE ( 'Fact Workday Transactions'[In Source Currency], __Denominator )
        )
    )

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Try troubleshooting like this:

     

    Invoiced GGR (€) =
VAR __Numerator = SUMX('Fact Workday Transactions',
'Fact Workday Transactions'[In Source Currency]
VAR __Denominator = 
LOOKUPVALUE('Exchange Rate'[Exchange Rate],
'Exchange Rate'[ExchangeRateDate],'Fact Workday Transactions'[Date],
'Exchange Rate'[CurrencyCode],'Fact Workday Transactions'[CUR]))
RETURN
__Denominator

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      hmm, thanks, but this is just giving me a value of 1 everywhere now. 

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Try like

    Invoiced GGR (€) =

    var _m =LOOKUPVALUE('Exchange Rate'[Exchange Rate],
    'Exchange Rate'[ExchangeRateDate],'Fact Workday Transactions'[Date],
    'Exchange Rate'[CurrencyCode],'Fact Workday Transactions'[CUR]))

    SUMX(divide('Fact Workday Transactions',
    'Fact Workday Transactions'[In Source Currency] ,if(_m=0, blank(),_m)))

     

    or handle with IFERROR

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hey,

       

      On this one, i dont get the second half of this at all... It doesnt seem to like it.

       

       

      why is such a simple calcultion so hard to do i wonder..sigh.

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Try this:

    Invoiced GGR (€) =
VAR __Numerator =
    SUMX (
        'Fact Workday Transactions',
        'Fact Workday Transactions'[In Source Currency]
    )
VAR __Denominator =
    LOOKUPVALUE (
        'Exchange Rate'[Exchange Rate],
        'Exchange Rate'[ExchangeRateDate], 'Fact Workday Transactions'[Date],
        'Exchange Rate'[CurrencyCode], 'Fact Workday Transactions'[CUR]
    )
RETURN
    IF ( __Denominator <> 0, DIVIDE ( __Numerator, __Denominator ) )

     

    Or this:

    Invoiced GGR (€) =
VAR __Denominator =
    LOOKUPVALUE (
        'Exchange Rate'[Exchange Rate],
        'Exchange Rate'[ExchangeRateDate], 'Fact Workday Transactions'[Date],
        'Exchange Rate'[CurrencyCode], 'Fact Workday Transactions'[CUR]
    )
RETURN
    SUMX (
        'Fact Workday Transactions',
        IF (
            __Denominator <> 0,
            DIVIDE ( 'Fact Workday Transactions'[In Source Currency], __Denominator )
        )
    )

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Icey  - Fantastic. Thank you so much!

       

      Actually i like the logic here of creating two VAR as numberator and denominator, I'll apply that logic for other calcs as its a great way to follow through on calcs later on.

       

      Really appreciate all comments and replies!

      Regards

      Ben