Power BI Challenge 9- Currency Conversion and More

Hello to all my fellow Power BI enthusiasts.

I trust you are all keeping well and safe.

I know that our American contingent will be eagerly awaiting the results of the election while I sit here contemplating another lockdown in the UK starting tomorrow :frowning:.

Fear not though, the Enterprise DNA challenge is here for its 9th instalment!

The last challenge was seriously amazing and there was some incredible value provided by all participants a massive well done again.

I’m going to keep banging on about this, but YOU NEED TO GET INVOLVED.

These challenges are created to help you and accelerate your learning with the help of the #BestPowerBICommunityByFar

First time participant winner

We are always looking to improve the challenges and get more of you involved and that’s why this week I want to bring to you an exciting new category courtesy of our very own @BrianJ.

Brian has kindly been sponsoring the newcomer prize however we felt that the uptake from the community wasn’t as we had envisaged.

We are therefor changing it up and giving even more of you an opportunity to get involved and bag yourself some goodies along with the recognition of being a challenge winner. The prizes on offer are as follows.

  1. A copy of the Definitive Guide to DAX, 2nd Edition (the indispensable "bible"of DAX) or your choice of any other book from the eDNA Forum Recommended List ; or
  2. A copy of SnagIt 2020 , a do-it-all screen capture and graphics tool used by the members of the expert team for screen grabs, annotation, gif and video generation, photo editing, etc. @nick_m got me hooked on this, and it has a million and one uses for Power BI report development; or
  3. A four-month subscription to FlatIcon.com - this is an online service with millions of downloadable and editable icons that you can use to really polish your Power BI reports.

Really simple, its open to everyone and anyone no matter what your background or experience, however you must be a member of Enterprise DNA an intern or part of the scholarship programme.

All you must do is let us know it’s your first challenge.

There is some amazing loot on offer not to mention the learning opportunity and free membership on offer.

We really want to see more of you get involved so even if it’s a post to the forum to join the conversation please do get involved.

To learn more about the challenge and how it works be sure to check out the forum below!

If you’re not sure of anything just reach out to me.

Remember: The weekly winner will receive a complimentary membership to the platform that they can share with anyone and the opportunity for your work to be showcased across our channels.

THE BRIEF

As I was reviewing some posts on the forum, I came across some absolute super content around currency conversion. There are some seriously amazing forum posts and videos available covering this technique.

This gave me a super idea for challenge 9.

So I had to dig up the archives and unearth a dataset that would not only allow you to review and implement the techniques but hopefully challenge you to ensure that you can apply the techniques regardless of the data set.

This is an actual dataset where sensitive details have been masked but there have been no further transformations carried out.

Requirements

The ask is to produce a report from the data above that gives the user the ability to select a currency and see the associated sales and cost.

The user wants the ability to know the conversion rate that has been used. This can be an average value.

Highlight any issues with the data.

Points to note

If there is data missing bring this to the users’ attention as we may not be aware of these issues.

Sales and cost values can be negative as the data is related to waste management.

Est weight -is the number of units that the business are buying. If there is a value in either Total Supplier Weight or Total Buyer Weight use that but alternatively take the Est Weight

While the data set is small there is a lot going on that will need distilling. So pay attention.

UPDATE

After some conversations and suggestions from the community I want to provide some further context to help you understand requirement.

The Business

The data has been taken from Enterprise DNA Recycle LTD (EDR).

EDR are a company who look to buy and help remove scrap and waste recyclables from clients and then sell these to other providers.

For certain items they incur a cost and for others they are actually paid to take away the materials. This is why there is negative values associated with the cost.

This is also true for the sales they make. Some materials are received by buyers who they pay to take the materials away.

To complicate matters further ERD work with clients around the globe and are therefore purchasing, selling and hauling in different currencies.

There is a very manual and onerous process to consolidate costs sales and profitability.

Below is a diagram of how the business works.

Breaking down the problem

As mentioned above that the business operates in a number of countries and therefore receive multiple currencies.

There is a lot of detail in the dataset however we can distil the key information as follows.

Ignoring all dimensionality to begin with the key columns are as follows.

Ref
Unit Purchase Price
Purchase Currency
Unit Sales Price
Sale Currency
Haulage Cost
Haulage Currency
Est Weight
Total Supplier Weight
Total Buyer Weight

Below is the top 10 rows extracted from the dataset for just the fields above.

image

REF 002 Example broken down:

Unit Purchase Price: £930

Unit Sale Price: €880

Haulage Cost: £450

Est Weight: 20

Calculation

Total Cost Amount = 20(Est Weight) * £930 (Unit Purchase Price) = £18600

Total Sales Amount = 20(Est Weight) * €880(Unit Sale Price) = €17600

Haulage Cost = £450

Now with the given data its hard to work out profitability as we have multiple currencies involved simply doing total costs and sales isn’t going to work.

Therefore what the business have is an exchange rates table and convert something along the lines of:

Euro 1 US Dollar 1.18
Euro 1 British Pound 0.9
British Pound 1 US Dollar 1.32
British Pound 1 Euro 1.11
US Dollar 1 Euro 0.85
US Dollar 1 British Pound 0.77

They want to report the figures in all three currencies.

£-Calculation

Total Cost Amount= £18600 -No conversion required

Total Sales Amount= £15840 (17600 *0.9 (exchange rate from table above)=15840)

Haulage Cost=£450 -No conversion required

Profits= -£3210 - Sales(£15840)-Costs(£18600+450)

€-Calculation

Total Cost Amount=20646 – (£18600*1.11(exchange rate from table above) =20646)

Total Sales Amount=€17600 - No conversion required

Haulage Cost=€499.50 (£450*1.11(exchange rate from table above) =499.50)

Profits= €-3545.5 Sales(€17600)-Costs(€20646 +€499.50)

$-Calculation

Total Cost Amount=$ 24552 -( £18600 * 1.32(exchange rate from table above) =24552

Total Sales Amount= $ 20768 – (17600*1.18(exchange rate from table above)=20768

Haulage Cost=$594 -( £450 * 1.32(exchange rate from table above)

Profits=$-4378 Sales(20768)-Costs(24552+594)

This then allows them to report in any currency dependant on the use case.

Data Model

The company are looking to leverage Power BI to help remove the manual effort of converting.

Ultimately the design of the model is up to you and I really don’t want to be to prescriptive or limit anyone’s thinking. So below I have shared a very high-level design to help facilitate your modelling efforts.

I have ignored the other dimensions for now but this is something you could easily add to enrich the data and reporting.

If any of you would like any further support please do not hesitate to get in touch.

SUBMISSION DUE DATE - Monday, 16th November 2020 (PST)

Please can you all submit your PBIX files to powerbichallenge@enterprisedna.co

The data set can be downloaded below.
Challenge 9 Data.xlsx (100.2 KB)

Best of luck!

Any issues or questions please reach out.

Haroon

Enterprise DNA

6 Likes

This is sure an interesting data set Haroon, will get my thinking hat on!

3 Likes

@haroonali1000,

This sounds like a great challenge, but somebody who has never dealt with currency conversion is going to be completely lost on this one. The definitions are helpful, but think there is much more explanation needed if we want to encourage participation. I’m even a little confused as where to start on this challenge. Looking forward to completing this challenge, but more information is needed in order to proceed.

Thanks
Jarrett

5 Likes

Challenge Participants,

FYI - both @sam.mckay and eDNA expert @Nick_M have done YouTube video series’ on currency conversion. Go to YouTube and search “Enterprise DNA currency”. I suspect these are all about to get a whole lot of hits…

  • Brian

6 Likes

That was my planned first stop too…
Greg

2 Likes

I’ve never done currency conversion myself so this will be a challenge, will check out @sam.mckay videos and some other resources.

1 Like

Hi All,

Thanks for the feedback I have added some more detail and context to the post above so please review.

Any clarifications or help needed do not hesitate to get in touch.

Thanks,
Haroon

3 Likes

Hi Harron,

Thanks for this interesting challenge, I have one question:

What about ‘null’ in Currency columns, for example there are about

19 records without purchase currency,
14 records without sale currency and
5 records without Haulage currency…

Hi @Mohammed_ali,

The suggested action would be to ensure you make the report viewer aware that such data exists.

I would personally exclude the records missing currency values from any calculations.

Thanks,
Haroon

1 Like

Hello all! Any recommendation for a good source where I can find daily exchange rates ?

https://www.x-rates.com/

4 Likes

@alexbadiu

7 Likes

Here is the data with daily exchange rates included Challenge 9 Data.xlsx (140.5 KB)

8 Likes

https://www.daxpatterns.com/currency-conversion/

6 Likes

so i’m going to try and use a data model based on the one in the pattern

3 Likes

Thanks for sharing @kentyler some great resources.

Thanks
Haroon

I found a simple way to get the historical fx rate using Google sheets .

The GOOGLEFINANCE function can be used to provide historical exchange rates. It will list the rate at the end of each day, for a period specified by you. This could be for a single day, a week, a month, or longer.

To do this, click on an empty cell and type =GOOGLEFINANCE("Currency:USDGBP", "price", DATE(YYYY,MM,DD), DATE(YYYY,MM,DD) , where the first nested DATE function is the start date, and the second DATE function is the end date.

Replace YYYY with the year, MM with the month, and DD with the day for both nested DATE functions. You’ll also need to replace the currency codes to match the currencies you’re looking to exchange .

Finally, you can import this in power bi using web import option.

Interested to know other options also…

2 Likes

one thing that seems unclear is just WHEN the transaction happens… for the sake of moving forward I’m going to go with estimated load date = transaction date
because i need a transaction date to calculate the currency exchange

1 Like

Dear all,
Please find below my submission for Challenge 9. This time I went for a dark background and a lot of interactivity through buttons and tooltips. I also used custom visuals to provide additional insight.

I had some problems when trying to publish to web. The main buttons on the left side of the screen do no work on Google Chrome (they work perfectly fine on PBI Desktop). They work on Internet Explorer and mobile phone. What is even weirder is that on Internet Explorer the colors of my tooltips change. I tried to rework my submission in the last 2 hours, remade the buttons, tried bookmarks, tried also to change my buttons and use Page Navigation. It just doesn’t want to work. Maybe it is a temporary bug.

LATER EDIT
I found a workaround and now the buttons work as they should. You can fully access the report by using the new link below.

Best regards
Alex






Publish to Web Link

11 Likes

Hello Everyone,

Here is my submission for challenge 9.

Here is a link to the report.

Thanks and Regards,
Nebiyu

11 Likes