COIT11240 dashboard design and visualisation Assessment 1

The tasks

You will use Power BI tool for data analysis and visualisation exercises provided in this assessment. These tasks will help to build your knowledge of data formats, storage, retrieval, analysis and visualisation techniques of dashboard design. 

You are required to work on the given PBIX file on the Moodle site. Download the file into your local machine and work on the file directly. 

For each task, use Power BI tool, generate the output by executing the Power BI functions on the given dataset in the PBIX file, and save the screenshots of the output. Save all output screenshots and analysis on the generated outputs in an MS Word file. This Word file is required to be submitted as a report for marking. Each task should be numbered correctly for marking. Also, your worked PBIX file is needed to be submitted in a zip format. 

The tasks are given as follows:

Data analysis:

1. Go to the data model in your giver PBIX file and open the data view to create a new table called “Calendar”. Create a column name Date automatically and insert dates from 4^th of Jan, 2010 to 11^th of May, 2017 into the Date Column.
2. Create a table name Weekdays and create a column name Weekday-Name (Hints: Make sure that weeks are numbered as follows: 1=Monday, 2=Tuesday and 3=Wednesday and so on and create a column name Weekday-Nr under the Weekdays table.
3. Create new column names Year, Quarter, Year-Quarter and Weekday-Nr in the Calendar
4. Go to the relationship view and create a 1:1 (one to one) relationship between the columns Date in the tables Apple-Combined and Calendar.
5. Create a *:1 (many to one) relationship between the columns Weekday-Nr in the tables Calendar and Weekdays.
6. Show and explore the relationships (i.e., Perform Edit and Delete functions) of data and tables with explanation. Provide a screenshot with an explanation. 
7. Create a calculated column named “End-vs-Start” which calculates the % change between the column Price-End of day and the column Price-Start of day.
8. Create a measure named Measure-Average Price-End, which calculates the average of the values in the column Price-End of day
9. Create two measures named Measure-Minimum Price and Measure-Maximum Price, which calculate the minimum/maximum value of the column Price-End of day

Report and visualisation

1. Create a line chart which can display the average price (Price-End of day) for each year and month. Write your explanation on the created chart.
2. Create a Line and stacked column chart visualisation, which displays the following information
   • Quarterly data from Q12010-Q22017
   • Columns which should display the average Price-End of day of the corresponding quarter.
   • A line which shows the average percentage change between the Price-End of day and the Price-Start of day for each quarter Write an explanation on the created chart. 
3. Create a Slicer to show the different Weekday-Names.
4. Create a Gauge Chart to display the minimum, the maximum and the average Price-End of day. Write an explanation on the created chart.
5. Create a matrix to show date, Price-Start of day and Price-End of Day.
6. Write a conclusion on your overall analysis and data visualisation.