cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
florindaniel96 Frequent Visitor
Frequent Visitor

Multiplying column by lookup value

Hi everyone,

 

I have a table which has one of the columns Currency code, so each line has a code like this : EUR, USD, AUD etc. I have another table with the FX Rates that has those codes as columns. The FX Rates table looks like this:

 

Date                AUD   USD     EUR    PLN

01.2019             x         y         z         v

02.2109             x         q         f         s

03.2019             ............. and so on

 

In my main table I want to multiply for example the GP (which is in local currencies) with the fx rate like this:

GP*SUM(column)

where column is one of AUD, USD, EUR etc. But I need it to lookup for which column should it multiply with automatically by looking into the main table at the currency code. Is this possible ?

 

2 REPLIES 2
Super User IV
Super User IV

Re: Multiplying column by lookup value

@florindaniel96 it will be easier if you unpivot your FX Rate table and then create a unique key by cocatenating Date and Currency code, and same key add in your transaction table. It will allows you to set relationship between these two tables on this new column and from there everything will be super simple






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





florindaniel96 Frequent Visitor
Frequent Visitor

Re: Multiplying column by lookup value

@parry2k I have already done that. But that will give me for example the USD Value of a project that has entries in May, multiplying the value with the fx rate also from May. But sometimes the users of the report want to see the values at a different fx rate than that from the current month as the entry. That's why I would have liked to create like a drop down list slicer so the user can select whatever date he wants for the fx rate. 

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors