cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
florindaniel96
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
Highlighted
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

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors