Power BI Challenge 9 - Currency Conversion and More from Hideo

Challenge Updates
1

Here’s Hideo’s entry for Power BI Challenge 9. @Hideo, feel free to add other details of your work.

image

image

image

image

Here is the link to the report:

And here’s how Hideo described it:

(First Screen)

Sales(grey line) and Profit(orange line) bar chart. I just wanted to see how are they related each other in daily basis as it is my first time business model with more sales leads to more negative profits.

There are three currency icons to switch.
*After I got professional advise from @alexbadiu I have modified chart title colour and main title alignment. Only quick small changes but this looks much nicer than before. Thanks to Alex.

(Table & chart icon)

Open table view by clicking the table icon and bar chart icon is for the slicer of this table.
Icon colour changes once open the charts and click the coloured icon to close the charts.
By doing this consumer does not need to move cursor far away every time they close.

(Truck icon)

Simple regression chart for Haulage and sales

(Len icon)

Another regression chart for sales and profits with having outliers.

To learn about the real-life scenario presented for the challenge, be sure to click on the image below.

power-bi-chal-1

2

Hello forum members,

Here is the details for the report.

(Data preparation)
–Ex-Rate
I have accessed Yahoo finance for the historical exchange and kept in CSV file.

Date GBP/USD EUR/USD USD/USD
1/02/2019 1.310925 1.145082 1
4/02/2019 1.307856 1.145725 1
5/02/2019 1.303084 1.143576 1
6/02/2019 1.29584 1.140875 1

– Currency format
Created currency format referred EDNA video.
image

(Model)
*To make use of Date Period I have disconnected “Date” and “ExRate” table.
without connecting these tables, Dax Lookupvalue have done very good job to achieve currency conversion

image
image812×461 23.6 KB

(Power Query)
*Need data clean up as I have notice some dates are missing in Delivery date, confirmed load date and Est Load date. So I have created some logic to use for currency exchange date.
image

*As this is my first challenge for the currency conversion, I wanted model as simple as possible.
So I have selected single currency model (USD) for the fact table this time.
https://www.daxpatterns.com/currency-conversion/#dax-formula

*To bring USD exchange rate to the fact table I have merged two tables using left outer join by Date & currency.
image

I have created three exchange columns for Purchase , sales and Haulage.

image

(Dax)
TotalSales =
SUMX(TradingF,
TradingF[SalesEX]*TradingF[Weight]*TradingF[Unit Sales Price])

ConvertedSales = 
var CurrentDate= MAX(TradingF[ExDate])
var currencyselected= SELECTEDVALUE('Format'[Ticker],"USD")
var CurrentExrate= LOOKUPVALUE(ExRate[Value],ExRate[Date],CurrentDate,
                                             ExRate[Currency],currencyselected)
var converttedsales= [TotalSales]*1/CurrentExrate
return converttedsales


FormattedSales = 
var sales= [ConvertedSales]
var formatting= LOOKUPVALUE('Format'[Format], 
                    'Format'[Ticker],SELECTEDVALUE(ExRate[Currency],"USD"))
var  AddonCurrency= SELECTEDVALUE('Format'[Ticker],"USD")
var result=CONCATENATE(AddonCurrency&"  ",FORMAT([ConvertedSales],formatting))
var clearblank= IF(ISBLANK([ConvertedSales]),BLANK(),result)
return clearblank

(I have used Z score rather than rankx or topn this time and found this is easy and useful)
Sales Z score =
var allave=AVERAGEX(ALLSELECTED(‘Date’),[ConvertedSales])
var allstd=STDEVX.P(ALLSELECTED(‘Date’),[ConvertedSales])
var zscore= DIVIDE([ConvertedSales]-allave,allstd,0)
return zscore

SalesOutlier = IF([Sales Z score]>3 || [Sales Z score]<-3,1,0)

(Overall)

As this is my first time to deal with currency conversion, eDNA is having plenty of good videos so I have learned a lot.
Also I really appreciate for all the members who are supporting each other.
This is a great community.
Thank you so much eDNA for giving me a learning opportunity.

Regards,

Hideo

3

Wow really impressive design here. I love how you have use the tooltips to showcase mini insights which align to the core report. The ability to showcase many of these insights together without getting distracted by multiple different report pages is really strong.

Like the navigation on the side as well I think the way that users can click through those elements to see different insights really easily is a super idea. I love how you’ve thought out of the box here and have definitely showcased to others how you can be far more creative around your report development.

I think the colors work well, the navigation is great, the information is high quality.

You’ve done a great job here well done

Sam

2 Likes