PowerBI Data Modeling Exercises - joeygomes-avanade/PowerBI-Training GitHub Wiki

Scenario

VanArsdel is a company that manufactures and sells sporting goods. The company has offices in the United States (US) and several other countries. Its sales comprise of US sales and International sales. VanArsdel’s sales come from its own manufactured products, as well as other manufacturers’ products.

You have successfully brought the US sales data from the Access database and the International sales data from a collection of CSV files to Power BI Desktop. Before you can start analyzing your data, you need to manage the table relationships within your data model and create new ones if necessary. To do so, you might need to create calculated columns or calculated tables for the relationships to be based on.

Once you have all the relationships created, you can create visualizations and start to analyze the data. However, you need to create additional measures to perform more advanced analysis with your data, which includes:

  • Comparing last year's sales and last year's YTD sales.
  • Comparing sales of VanArsdel's manufactured goods to other manufacturers.

Overview

In this lab, you will create calculated columns, calculated tables, and create table relationships in your data model based on the calculated columns and tables you created. In addition, you will write several DAX expressions to create measures to be used to analyze VanArsdel’s sales data. Specifically, you will create the following measures:

  • Total Sales: calculates the total sales.
  • LY Sales: calculates last year sales.
  • Sales Var: calculates sales variance between this year and last year sales.
  • Sales Var %: calculates sales variance between this year and last year sales in percentage.
  • YTD Sales: calculates YTD sales.
  • LY YTD Sales: calculates last year YTD sales.
  • YTD Sales Var: calculates sales variance between this year and last year YTD sales.
  • YTD Sales Var %: calculates sales variance between this year and last year YTD sales in percentage.
  • Total VanArsdel Sales: calculates sales for VanArsdel manufactured goods.
  • % Sales Market Share: calculates the percentage of VanArsdel manufactured goods from total sales. Before starting this lab, you should review the Power BI Desktop Modelling module in this course. Then, if you have not already done so, follow the instructions in the Set up the Lab Environment section of this course to set up the lab environment.

Downloads

  • All exercises - PowerBI_Starting.zip -

Exercise 1 - Manage Table Relationships

Power BI Desktop has automatically detected and created table relationships. So the first step is to ensure all the relationships are properly created, and if not, create them yourselves.

  1. Start with the "Starting.pbix" file found after extracting the PowerBI_Starting.zip file.
  2. Open the Starting.pbix file.
  3. In the navigation pane on the left, click Model.
  4. Notice that there is a many to one relationship from the ProductID column on the Sales table to the ProductID column on the Products table.
  5. Notice that there is a many to one relationship from the ManufacturerID column on the Products table to the ManufacturerID column on the Manufacturers table.
  6. Drag the Date column on the Sales table to the Date column on the Date table. Now you want to create a relationship between the Sales table and the Locations table. First, you merge the Country and Zip columns in both Sales and Locations table as a new column, CountryZip. Then, you create a relationship on the CountryZip column for both tables.
  7. In the navigation pane on the left, click Data.
  8. In the navigation pane on the right, click Location.
  9. On the Modeling ribbon, click New Column.
  10. In the formula bar for the new column, type CountryZip = Location[Country] & ", " & Location[Zip], and press Enter.
  11. In the navigation pane on the right, click Sales.
  12. On the Modeling ribbon, click New Column.
  13. In the formula bar for the new column, type CountryZip = Sales[Country Name] & ", " & Sales[Zip], and press Enter.
  14. In the navigation pane on the left, click Model.
  15. Drag the CountryZip column on the Sales table to the CountryZip column on the Locations table.
  16. Click Save, to save the Power BI file.

Exercise 2- Last Year Comparison

You want to know how much sales (revenue) in total does the VanArsdel have and to compare this with the figure from the same period last year. You need to create several calculated measures to help with this comparison. To do so, in either the Report view or the Data view, right-click the Sales table, click New Measure, and type in the corresponding DAX formulas for the measure you want to create. This will create the measures with the Home Table properties set to the Sales table. Specifically, you will create the following measures:

  1. Total Sales: calculates the total sales. Format this measure as Currency. (Hint: Check out the SUM function).
  2. LY Sales: calculates last year's sales. Format this measure as Currency. (Hint: You might find the CALCULATE and SAMEPERIODLASTYEAR function useful).
  3. Sales Var: calculates sales variance between this year and last year's sales. Format this measure as Currency. (Hint: This is simply the difference between Total Sales and LY Sales).
  4. Sales Var %: calculates sales variance between this year and last year's sales in percentage. Format this measure as Percentage. (Hint: This is simply the percentage of Sales Var from LY Sales. You might find the DIVIDE function useful).
  5. If it is not already open, open the Starting.pbix file.
  6. In the navigation pane on the left, click Data.
  7. In the navigation pane on the right, click Sales.
  8. On the Modeling ribbon, click New Measure.
  9. In the formula bar for the new measure, type Total Sales = SUM(Sales[Revenue]), and press Enter.
  10. On the Modeling ribbon, click Format, click Currency, and click Currency general.
  11. On the Modeling ribbon, click New Measure.
  12. In the formula bar for the new measure, type LY Sales = CALCULATE([Total Sales],SAMEPERIODLASTYEAR(‘Date’[Date])), and press Enter.
  13. On the Modeling ribbon, click Format, click Currency, and click Currency general.
  14. On the Modeling ribbon, click New Measure.
  15. In the formula bar for the new measure, type Sales Var = [Total Sales] - [LY Sales], and press Enter.
  16. On the Modeling ribbon, click Format, click Currency, and click Currency general.
  17. On the Modeling ribbon, click New Measure.
  18. In the formula bar for the new measure, type Sales Var % = DIVIDE([Sales Var],[LY Sales]), and press Enter.
  19. On the Modeling ribbon, click Format, and click Percentage.
  20. Click Save, to save the Power BI file.

Tips? If you're stuck, check out the following DAX expressions to calculate similar measures, but on Units instead of Revenue. Don't copy paste the tips to your PowerBI file, but try to type the formula by yourself.

  • Total Units: Total Units = SUM(Sales[Units])
  • LY Total Units: LY Total Units = CALCULATE([Total Units],SAMEPERIODLASTYEAR(‘Date’[Date])
  • Total Units Var: Total Units Var = [Total Units] - [LY Total Units]
  • Total Units Var %: Total Units Var % = DIVIDE([Total Units Var],[LY Total Units])

Exercise 3 - Year to Date

Year-to-date (YTD) is a period starting from the beginning of the current year and continuing up to the present date. You want to calculate the YTD sales and compare this with the figure from the same period last year. Specifically, you will create the following measures:

  1. YTD Sales: calculates the YTD sales. Format this measure as Currency. (Hint: Check out the TOTALYTD function).
  2. LY YTD Sales: calculates last year YTD sales. Format this measure as Currency. (Hint: You might find the CALCULATE and SAMEPERIODLASTYEAR function useful).
  3. YTD Sales Var: calculates sales variance between this year and last year YTD sales. Format this measure as Currency. (Hint: This is simply the difference between YTD Sales and LY YTD Sales).
  4. YTD Sales Var %: calculates sales variance between this year and last year YTD sales in percentage. Format this measure as Percentage. (Hint: This is simply the percentage of YTD Sales Var from LY YTD Sales. You might find the DIVIDE function useful). Need a Little Help? If you're stuck, check out the following DAX expressions to calculate similar measures, but on Units instead of Revenue.
  • YTD Total Units: YTD Total Units = TOTALYTD([Total Units],‘Date’[Date])
  • LY YTD Total Units: LY YTD Total Units = CALCULATE([YTD Total Units],SAMEPERIODLASTYEAR(‘Date’[Date]))
  • YTD Total Units Var: YTD Total Units Var = [YTD Total Units] [LY YTD Total Units]
  • YTD Total Units Var %: YTD Total Units Var % = DIVIDE([YTD Total Units Var],[LY YTD Total Units])

Exercise 4 - Market Share

VanArsdel’s sales comprise of products manufactured by VanArsdel and other companies. You want to know how much of these sales are VanArsdel’s own manufactured products. You decide to show this share in numbers and %. Specifically, you will create the following measures:

  • Total VanArsdel Sales: calculates sales where the manufacturer of the product is VanArsdel. Format this measure as Currency. (Hint: Use the CALCULATE function and filter by Manufacturer).
  • % Sales Market Share: calculates the percentage of sales of VanArsdel manufactured products from the total sales. Format this measure as Percentage.
  1. If it is not already open, open the Lab 2 - Starting.pbix file.
  2. In the navigation pane on the left, click Data.
  3. In the navigation pane on the right, click Sales.
  4. On the Modeling ribbon, click New Measure.
  5. In the formula bar for the new measure, type Total VanArsdel Sales = CALCULATE([Total Sales],Manufacturer[Manufacturer]=“VanArsdel”), and press Enter.
  6. On the Modeling ribbon, click New Measure.
  7. In the formula bar for the new measure, type % Sales Market Share = IF([Total VanArsdel Sales]=0,0,DIVIDE([Total VanArsdel Sales],[Total Sales],0)), and press Enter.
  8. On the Modeling ribbon, click Format, and click Percentage.
  9. Click Save, to save the Power BI file. If you're stuck, check out the following DAX expressions to calculate similar measures, but on Units instead of Revenue.
  • Total VanArsdel Units: Total VanArsdel Units = CALCULATE([Total Units],Manufacturers[Manufacturer]=“VanArsdel”)
  • % Units Market Share: % Units Market Share = IF([Total VanArsdel Units]=0,0,DIVIDE([Total VanArsdel Units],[Total Units],0))

Exercise 5 - Optimize the Data Model

Now that you have the table relationships defined and the measures created, you want to optimize the data model before you create the visualizations.

  1. If it is not already open, open the Starting.pbix file.
  2. Ensure both the International Sales and Country Population table are hidden from the report view using the following steps.
  3. In the navigation pane on the left, click Data.
  4. In the navigation pane on the right, click the ellipses (…) adjacent to International Sales and click Hide in report view.
  5. In the navigation pane on the right, click the ellipses adjacent to Country Population and click Hide in report view.
  6. Hide the following fields on the Date table from the report view:
  • MonthNo
  • MonthID
  • Month
  1. In the navigation pane on the right, click Date, click the ellipses adjacent to MonthNo and click Hide in report view.
  2. In the navigation pane on the right, click Date, click the ellipses adjacent to MonthID and click Hide in report view.
  3. In the navigation pane on the right, click Date, click the ellipses adjacent to Month and click Hide in report view.
  4. Sort the MonthName column by the MonthNo column using the following step.
  5. Click the MonthName column and, on the Modeling ribbon, click Sort by Column, and click MonthNo.
  6. Hide the CountryZip field on the Location table and the ManufacturerID field on the Manufacturer table from the report view using the following steps.
  7. In the navigation pane on the right, click Location, click the ellipses adjacent to CountryZip and click Hide in report view.
  8. In the navigation pane on the right, click Manufacturer, click the ellipses adjacent to ManufacturerID and click Hide in report view.
  9. Hide the following fields on the Products table from the report view.
  • ProductID
  • ManufacturerID
  • Manufacturer
  1. In the navigation pane on the right, click Products, click the ellipses adjacent to ProductID and click Hide in report view.
  2. Click the ellipses adjacent to ManufacturerID and click Hide in report view.
  3. Click the ellipses adjacent to Manufacturer and click Hide in report view.
  4. Hide the following fields on the Sales table from the report view.
  • ProductID
  • Date
  • Zip
  • Units
  • Revenue
  • Country Name
  • CountryZip
  1. In the navigation pane on the right, click Sales, click the ellipses adjacent to ProductID and click Hide in report view.
  2. Click the ellipses adjacent to Date and click Hide in report view.
  3. Click the ellipses adjacent to Zip and click Hide in report view.
  4. Click the ellipses adjacent to Units and click Hide in report view.
  5. Click the ellipses adjacent to Revenue and click Hide in report view.
  6. Click the ellipses adjacent to Country Name and click Hide in report view.
  7. Click the ellipses adjacent to CountryZip and click Hide in report view.
  8. Click Save, to save the PowerBI file.