Here’s Nebiyu’s entry for Power BI Challenge 9. @Neba, would you like to share how you built this dashboard and what your inspiration is in building it?
Here is the link to the report:
To learn about the real-life scenario presented for the challenge, be sure to click on the image below.
@EnterpriseDNA,
Here is my write up for Challenge 9,
This was a complex Dataset. It took me some time to come up with a report but it was a learning experience and I truly enjoyed it. Thanks again for these challenges.
Edna Waste management LTD is doing business in different currencies and the requirement was to analyze transactions in all the currencies.
Data Model :-
I used Power Query to extract unique values and to create the following dimension tables:-
Data Model:-
The Currencies table had an active relationship with Sales currency column and inactive relationship wit purchase and haulage currencies.
Currency exchange rate table has no direct relationship with the fact table instead it has a relationship with currencies and dates tables.
For currency exchange rates, I downloaded a currency rate Excel sheets from ExcelRates.com for each currency rates referencing the minimum date and maximum dates from the source table ( ‘01/01/2019’ and ‘02/28/2020’ respectively ).
Then I merged all the currency rates tables and unpivoted the columns which gave me currency rates from one currency to another for each day.
DAX Calculations:-
For the all the sales and other transactions in different currencies DAX calculations, I used average rate for all the dates and each currency rate.
For instance, for Pound sterling to Euro exchange rates The below DAX calculation was used :-
Avg. GBP to Euro =
CALCULATE(
AVERAGEX( ‘Currency Conversion Table’ ,
‘Currency Conversion Table’[Exchange Rates]
) ,
FILTER(
ALL( ‘Currency Conversion Table’ ) ,
‘Currency Conversion Table’[Currency Pairs] = “GBP to Euro” ) )
For Sales by each currency, I used
price * estimated weight
For Instance, for Sales in Euro the following DAX was used ,
Sales in Euro =
CALCULATE(
SUMX( EDR_Fact ,
EDR_Fact[Unit Sales Price] * EDR_Fact[Est Weight] ) ,
EDR_Fact[Sale Currency] = “Euro”
, EDR_Fact[Sale Currency] <> “No Currency” )
To convert the sales in Euro to other currencies, for instance, sales in Euro was multiplied by Average exchange rate.
Sales in Euro was converted to USD this way:-
Sales Euro to USD = [Sales in Euro] * [Avg. Euro to USD]
I created all the sales in different currencies and converted each amount to other currencies like above.
For the costs the same method was used except I added Haulage cost to the total based on the currency. ( I used the same calculations as above for the Haulage costs )
E.g.
Cost in GBP =
CALCULATE (
SUMX ( EDR_Fact,
( EDR_Fact[Unit Purchase Price] * EDR_Fact[Est Weight] )
),
EDR_Fact[Purchase Currency] = “GBP”,
USERELATIONSHIP ( EDR_Fact[Purchase Currency], Currencies[Currency] )
) + [Haulage in GBP]
Visualizations :-
The first page of the report shows Total sales, costs and profit/loss converted to each currency and
transactions in each currency.
I used different color themes for each currency so the end user can identify what they are looking at by the color themes on the pages.
They can also see details of each currency transactions by clicking a button that takes them to a details page.
I used tooltips to show converted values for each currency to another on the Matrix tables.
Thanks and Regards,
Nebiyu
P.S. My apologies, I tried to format the DAX formulas but it goes back to unformatted formula when I submit.
Well done here. I hope that you learned a lot diving into some unique data. I think you’ve set out your report page really effectively. I love the grid system, the labeling system and the colors.
It’s super easy on the eye when you’re looking across all the different report pages so I commend you on how you’ve set things up.
Thanks for that description about how you went through the challenge. One thing by looking at your image of the data model, maybe there’s some simplification that can be done here just to clean this area up a little bit. Have you checked out the waterfall technique that I talk about in the advanced data transformations course?
Some simple updates here might make this a little bit easier to understand. But overall I think you’ve done a superb job so really well done and Congrats on what you’ve submitted.
Sam
@sam.mckay,
Thank you so much for your feedback Sam. As you mentioned , I didn’t model the data in a star schema.
I tried but the data was a little too complex for me so i went with snowflake ( Which is a no no in Power BI I hear).
I am looking at how the other participants created the data model and learning some other techniques.
P.S. we missed yours this time. Hopefully you will have time to inspire us on the next one.
Regards.