Power BI Test

Download the sample data file here.

Data Preparation and Data Modeling

1. Load 2 tables (orders and returns) in Power BI from the excel file
2. Create a column for both tables that will combine the text from “Invoice No” and “Stock Code” – this will serve as your key in making relationship between the two tables *Note: This should be done in Power BI Desktop*
3. Create your data model by connecting two tables in the model tab (regardless of Cardinality).

DAX Measures and Columns

1. 1. Invoice Month – create a column that will get the month and year of the Invoice Date (e.g. if the Invoice Date is 02/01/2011,  the format should be January 2011)
   2. Total Order Quantity – create a calculated measure that will get the total order quantity
   3. Total Returns – create a calculated measure that will get the total returns
   4. Revenue – create a calculated column that will get the revenue per row/order
   5. Total Sales – create a measure that will get the total sales (convert this to currency in USD)
   6. Return Rate – create a measure that will get the return rate (Total Returns/Total Orders) (this should be in Percentage format)

Visualization

1. Create cards that will show:
   • Total Sales
   • Total Order Quantity
   • Total Returns
   • Return Rate
2. Create a dropdown slicer for:
   • Status
   • Country
   • Month
   • Stock Code
   • Invoice No
3. Create a clustered column chart for Month-on-Month Sales
4. Create a line and clustered column chart for Month-on-Month Total Order Quantity, Total Returns and return rate
5. Create a clustered bar chart for the total returns per country

Note: You can edit the dashboard based on your preference (that should be visually appealing)

Submission

Send your completed file to applications@bruntwork.co

Subject line: “Power BI Test – [Your Name]’