Creating new column or measure where I divide by a locked cell; how to? (should be easy I guess...)

[Anonymous]

Hi peeps,

I have input currrency data that comes in excel format. I need to create a measure or a calculated column, which one is easier, that does what the formula in the attached picture does. I have column B and D as input data and want to create a measure or column that gives me every currency in Euro.

  Could anyone assist with this? 🙂

[Sean]

@Anonymous

You can use this Measure

Currency / Euro MEASURE =
IF (
    HASONEVALUE ( 'Table'[Currency Code] ),
    DIVIDE (
        CALCULATE (
            SUM ( 'Table'[Currency / USD] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Currency Code] = "Euro" )
        ),
        SUM ( 'Table'[Currency / USD] ),
        0
    )
)

Hope this helps! 

[Anonymous]

Hi!

Thanks for this. Like the other suggestion this formula also worked by i cannot use it with my LOOKUPVALUE formula. Any suggestions on my reply to the post above?

Thanks a lot!

Best,

Morten

[jthomson]

This'll do it as a calculated column, probably workable as a measure as well which'd be preferable I'd have thought

[Anonymous]

Hi, thanks a lot for the response. The suggestion you made worked for what I've asked but I encounter a problem when I apply the LOOKUPVALUE formula which is part of my model.

I have one table with invoice amount in all different currencies ("Invoice Data") and one table with exchange rates. I used the columns already in the exchange rate table with the formula below and it worked fine. Why does it not work now? Is using a calculated column a problem with this formula? (The calculated column is placed in the same table as the previous exchange rates I've used.

Amt. Open =
    sumx('Invoice Data;
        'Invoice Data[Amount open]*LOOKUPVALUE('Exchange Rates'[CCY/GBP];'Exchange Rates'[CCY];'Invoice Data'[CCY]))

If anyone else has any suggestions please let me know!

Thank you!