Use this assessment as a free knowledge check for basic report developer skills in Power BI Desktop. This page includes a link to a sample mini dataset as well as instructions for building a simple report.
By accomplishing each step, you will demonstrate knowledge of core fundamentals of using Power Query, DAX with evaluation context, and report design.
Open the below data set tables in Excel Desktop and save to your local PC. The workbook may open with some tabs hidden, just click the left-arrow to ensure all workbook tabs are visible. Review each tab which represents the collection of tables necessary to complete the below report.
The final report should look something similar to the image below:
Before you begin, please note that measures in the steps below are identified in [brackets], while table column names are identified in ‘single quotes’.
Please complete the following steps:
- Open Power BI Desktop and import the SalesTable, CustomerTable, ProductTable, TimeTable, and BranchTable as indicated by the blue header in each table icon.
- Make appropriate transformations in Power Query. Eliminate unnecessary columns to reduce table size and cardinality. Set appropriate field types, and address any visible query errors before loading tables into Power BI Desktop.
- You should notice one cell error in the Customer Table that will prevent establishing a relationship with the Sales Table. Resolve this error in Power Query.
- Close and load all table from Power Query into Power BI.
- Note: There are two date columns in the sales table, ‘Ship date’ and ‘Order date’. Use the ‘Ship date’ to connect with the Time Table.
- From the Power BI model view, create a valid star schema with the appropriate relationships.
- Create a Measure table to isolate your measures apart from the tables in the model and add the following DAX measures:
- Total Sales (‘Unit Price’ x ‘Sales Qty’)
- Total Discounted Sales (‘Unit Price’ x ‘Sales Qty’ discounted by ‘Customer Disc’)
- Year to Date Discounted Sales (Accumulated by month in the year from the Time Table)
- Year to Date Prior Year Discounted Sales
- Total Cost of Sales (‘Unit Cost’ x ‘Sales Qty’)
- Margin Dollars ([Total Discounted Sales] – [Total Cost])
- Margin Percentage ([Margin Dollars] divided by [Total Discounted Sales])
From the Customer Table, create a calculated column named ‘Customer Size’ that will represent the size classification of each customer in the customer table.
- Use DAX variables to help break up the steps to arrive at the final size class for each customer.
- Include a variable step that calculates the prior 12-month sales for each customer.
- Include a variable step that uses the SWITCH function to return the following parameters based on the prior 12-month sales step:
- “Large” Greater than or equal to $10,000.
- “Small” Less than or equal to $5,000.
- Otherwise “Medium”.
Create a matrix that displays the row labels ‘Cal Year’ above ‘Month Name Short’ from the Time Table Ensure the years and months are in calendar sequence. Add the title to the matrix “Total Sales by Month and Year”.
Add the following measures into the matrix value area:
- [Total Sales]
- [Total Discounted Sales]
- [Margin Percentage]
- [Year to Date Discounted Sales]
- [Prior Year to Date Discounted Sales]
Add Slicers allowing users to filter report by Product ‘Vendor’ and Product ‘Class’ in the same slicer. Turn on the search feature and make this slicer multi-select without needing to use the Ctrl key.
Add another Slicer allowing users to filter report by ‘Cal year’ and ‘Cal Qtr Name’.
Lastly, add a line graph that displays [Total Discounted Sales] in the Y axis, and ‘Mo Name Short’ in the X axis. Use ‘Cal Year’ in the graph as the legend to create separate line for each calendar year.
That’s it, good luck! If you have any questions about this assessment, please e-mail: mark@storybi.com
Please allow 24 hours for a response.