LOOKUPVALUE

[Anonymous]

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

[Icey · Accepted solution]

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]

@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

[amitchandak]

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]

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.

[Greg_Deckler]

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]

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