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.
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.
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.
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.
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.
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.
After some conversations and suggestions from the community I want to provide some further context to help you understand requirement.
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.
Unit Purchase Price
Unit Sales Price
Total Supplier Weight
Total Buyer Weight
Below is the top 10 rows extracted from the dataset for just the fields above.
REF 002 Example broken down:
Unit Purchase Price: £930
Unit Sale Price: €880
Haulage Cost: £450
Est Weight: 20
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:
|British Pound||1||US Dollar||1.32|
|US Dollar||1||British Pound||0.77|
They want to report the figures in all three currencies.
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)
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)
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)
This then allows them to report in any currency dependant on the use case.
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.
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 firstname.lastname@example.org
Best of luck!
Any issues or questions please reach out.