Explore the Excel Data Model

Download and open file

You should see a blank worksheet when opening the file. Open the Data Model by selecting the Manage Data Model icon from either the Data or PowerPivot tabs in Excel 2016; from the PowerPivot tab in Excel 2013; or the Power Pivot Window icon from the PowerPivot tab in Excel 2010.The data has been loaded to the Excel data model. You don’t need to refresh or edit the data.

How many tables were loaded into the data model?

7

How many rows were loaded into the FactInternetSales table?

60398

The FactInternetSales table contains the OrderQuantityList Price, and Product Cost for each row in the table. However, it does not have the total sales and cost value for each row in the table. 

  1. You want to calculate the total sales for each row of the FactInternetSales table. You do this by creating a calculated column named Revenue in the table, which multiplies the OrderQuantity and List Price
  2. You want to calculate the cost for each row of the FactInternetSales table. You do this by creating a calculated column named Cost in the table, which multiplies the OrderQuantity and Product Cost.
  3. Format these columns as currency.

Now that you have created the calculated columns needed for analysis, you are ready to create your Pivot table report. (Hint: while you are still in the PowerPivot window, from the Home tab on the menu ribbon, click on the Pivot Table icon. This will return you to your Excel window with a blank PivotTable and Field List).

  1. Create a Pivot table showing the list of Product categories (EnglishProductCategoryName) in the column, list of Countries (EnglishCountryRegionName) in the rows, and the total sales figure (Sum of Revenue). 
  2. Add a slicer to filter by Year (CalendarYear).

Including the all data for all years (i.e. do not filter the year), show the ranking of the sales for the top four countries

What happens in 2007 year?

Up to now, you have shown the sales figures in the Pivot table. These figures dynamically change according to the filtered context. The totals for sales is an implicit measure. You are now interested to see what the margin % of the product by country is. You will need to create an explicit measure to show the margin %.

  1. Create an explicit measure named Margin in the FactInternetSales table. This measure calculates the percent profit, which is the total revenue minus the total cost, and divide it by the total cost. (Remember, our definition for Margin is different than what most would consider standard). 
  2. Format the Margin measure as a %.
  3. Add this newly created measure to the Pivot table.

ITS Done for now wait till next tutorial bye.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s