Business Intelligence Fundamentals 2023-2024 PDF

Business Intelligence Fundamentals 2023-2024 Contents 1 Introduction............................................................................................................................................... 3 2 Exploration of the data files and simple calculati...

Business Intelligence Fundamentals 2023-2024 Contents 1 Introduction............................................................................................................................................... 3 2 Exploration of the data files and simple calculations................................................................................ 4 3 Pivot tables: the corner stone of business intelligence............................................................................. 9 A Creation of a first pivot table................................................................................................................. 9 B Creation of a pivot table linked to a data source................................................................................ 14 C Pivot table linked to multiple data sources, with extra calculations................................................... 20 D Creation and use of hierarchies........................................................................................................... 33 E How to use a time dimension.............................................................................................................. 39 F YEARS, QUARTERS, MONTHS and RUNNING TOTALS.......................................................................... 45 4 Parallel periods........................................................................................................................................ 57 5 Using Query............................................................................................................................................. 67 6 Using a datasource by means of query................................................................................................... 72 7 Using Query to change data or to create extra data............................................................................... 73 8 Using KPI's................................................................................................................................................ 78 9 Training exercise...................................................................................................................................... 83 10 Demo: Excel Powerpivot makes life easy............................................................................................ 89 A What's the problem ?.......................................................................................................................... 89 B Powerpivot comes to the rescue......................................................................................................... 89 C What have we learned ?...................................................................................................................... 91 2 1 Introduction Learn to use excel If you’re not familiar with excel yet, try to learn it within 14 days. At least, you should be able to make sums, averages and a simple graph. Your teacher can give you advice. Importance of excel / PowerPivot Spreadsheets and graphs play a very important role in companies. You surely will need all your knowledge of excel in your future job and also during your studies. Excel is one of the most handy software's that exist. Practice and it will prove to be a true friend. It helps herding your data. This is your data in the shape you get it from the ICT guys: The same data, after some efforts in excel-PowerPivot. This look more like “information”! TO DO o Install “today” excel 365 on your laptop or home computer. Consult Error! Hyperlink reference not valid.for free software: https://portal.office.com/account o Complete the assignments weekly. Don't work last minute, try to create high quality solutions and deliver them timely. Some question on the exam will resemble the weekly assignments, so practicing can be very 3  rewarding. In case your score on the exam is close to 10/20 (e.g. 9.5/20) your teacher will look at the assignments you posted. (criteria: quality and timely delivery) o Ask questions DURING the classes (ask questions orally, not by mail, not after the end of the course) o If there are English words you don’t understand: ”Google translate” is your friend. Next, you can also ask confirmation to your teacher. o When you are behind schedule due to illness, special circumstances… write a brief explanation (with medical certificate if possible) and post it on Canvas in the assignment “special information and certificates”. Further you have to complete and post all the assignments as soon as possible. 2 Exploration of the data files and simple calculations On Canvas you will find a folder "starter kit_1.zip". Put this zip on your home drive or flash drive (USB stick). Unzip the file and put the folders in a proper place. Open "workbooks part1_A /001_A_data.xlsx ". A is the series of the exercise. Later, B and C series will follow. In series A, you just follow the instructions in the course step by step. In exercises of series B and C, there will be less step- by-step instructions. Save as 001_A_your_name_first_name_classgroup.xlsx (e.g. 001_A_Vandermeiren_Chris_1BLX.xlsx) Follow this naming convention strictly! 1. At the bottom, select the sheet "Customers" 2. What is the meaning of these columns? a. Context: the company we study is a wholesaler of clothes and shoes. b. CustID: Customer Identification = The customer's unique identification number. c. Customer: the customer – a shop – that ordered our product(s) d. CustomerContactName: the shopkeeper/shop manager. 3. Order on CustID. (Just click anywhere in the column CustID. Then sort.) Do you see two customers with the same ID? Why not?(1) 1 Each Customer has 1 unique number. Otherwise customers with the same name could not be distinguished easily. And on the other hand, computers use numbers (not text, such as a name) to link all the data. 4  4. Order on CustomerID. Which company comes on top? (2) 5. Order in such a way you can count easily how many Customers are from Argentina. (3) We will experiment with the data, by creating some extra fields that are important for the business. 6. Select the sheet. a. What is the meaning of these columns? OrderID: Order Identification: the unique number of the order. In nearly all files provided by ICT-professionals, you can find a “key-field”, which is a unique number that uniquely identifies the objects (e.g. the order, the customer, the product). 7. Order by OrderID. 8. Which customer gave order 10248? (4) a. As you see, the same OrderID (10248) appears 3 times, because this order contains 3 order lines. Each order line is about a specific product. The first line is an order line of 5 Kimono’s. The cost (purchasing cost + handling cost) of these 5 Kimono’s (in total) is € 56.23 and the Sales price (paid by the customer) is € 63.55 (in total). b. Cost: purchasing price the company pays to its supplier + handling cost ; Sales > Cost, because otherwise, there would be no profit. c. OrderDate is 3 times the same, because the order (and its 3 order lines) is registered at 1 particular day in the ICT-application of the sales representative (sales representative = employee). 9. Order by ProductID/OrderID. This a sort on 2 (ordered) fields! Use the custom sort: Add Level: This sorting order is important to stay synchronized with the screenshots of the course. Check that you have the following sequence: 2 Eintrach GS 3 3 4 Dr Jims Trousers 5  What is the unit price (=price of 1 individual product item) of "Lenin Jeansshorts"? Create a new column "Unit price": Discounts and promotions can be reasons for the unit price to vary. 10. Put this column "Unit price" out of the way, by cut/paste to column P. 11. Create a column "Margin": Explanation: margin is what you earn on the sales transaction. It is what the customer pays minus the cost of the goods (price payed to the supplier + the handling costs). 12. Create a column "Margin per unit" Use the "fill handle" to fill all the cells up to line 2173. 13. Create a column "Margin pct": and format it as a percentage. 14. Create a column "Cost per unit": Explanation: this is the cost to buy/produce 1 unit of the product. 15. Use cut/paste to put the column "Unit price" next to "Cost per Unit". Rename "Unit price" to "Unit Sales price". 6 Save 001_A_your_name_first_name_classgroup.xlsx Post it on Canvas. The data is contained in a rectangular area and can be sorted All objects (orders, customers) have a unique ID; e.g. 1 customer => 1 CustID; 1 order → 1 OrderID An orders contains 1 or more order lines. The meaning of several financial terms: o Sales: what the customer pays o Cost: purchasing price the company pays to its supplier + handling cost o Margin = sales - cost o Margin (percentage) = (sales - cost) / sales (expressed as a %) formatting (e.g. percentage) o Unit Price = sales/quantity o Margin per Unit = margin/quantity o Cost per Unit = Cost/quantity 7 How many digits after the comma? Question: How should numbers be rounded? When are one or two digits after the comma required? Answer: When you are purchasing or selling, each cent counts. Then, no rounding is not allowed. But when you are creating a management report, showing the global picture is more important than sticking to little details. When talking about millions, cents don't count. Thus, consider the following examples: We could say "the price of this car is cars is 20.655,23 euro and the price of the other one is 15.456.89 euro". But is this information easy to remember? It would better to say "the prices are approximately 20.500 and 15.500 euro". That is easier to remember. The rule is: try to indicate 3 digits for each number. 15.500 is easier to grasp than 15.565 and much easier than 15.565,56. 15.500 => 3 digits: 15.500 With percentages: a profit margin of 19% is easy to understand. 19.6% is more precise, a little harder to remember. 19.64% would really be too detailed. 19.6% => 3 digits. Don't mention 19.64%, as this is too detailed. If a percentage is low, e.g. 0.00693%, then consider two options: 0.0069% (only two digits if this is detailed enough) or 0.00693% if accuracy is important. 8 3 Pivot tables: the corner stone of business intelligence A Creation of a first pivot table When you create a report/graph/dashboard, … you have to go through some standard procedures: At the end of an exercise, you should clearly see which message there is in the tables/graphs. This message should have a special meaning in the business context. What is our company doing well? What are dangers/threats? Where are the opportunities? And although the tables/graph should speak for themselves, you should be able to present the message (story) live for your colleagues. A story has more value than a spreadsheet delivered by mail. For each exercise, we will go through most of these stages. What is now, in this exercise, the business question? For a producer, high-margin products are important, because these can contribute greatly contribute to the profit. Sales of € 10.000 of a product with a margin of 10%, contributes € 1000 to the profit. At a margin of 1%, the contribution would be merely € 100. Sales € 10.000 → margin of 10% profit of € 1.000 Sales € 10.000 → margin of 1% profit of € 100 The business question is: “Compare the margin percentage of the different products. Which products are we selling at a high margin? Which are the low margin products?” 9 What data is available? Open 002_A_data.xlsx from starter kit 1 – workbooks part1_A. (Because the previous spreadsheet is rather long, we will start from a shorter one.) Save as 002_A_your_name_first_name_classgroup.xlsx Draft of the tables/graph The graph could like this: (bars are margin percentages) Sales margin per Product 25,0% 23,3% 19,1% 19,0% 19,0% 19,0% 19,0% 18,1% 17,6% 20,0% 15,9% 14,7% 13,6% 13,5% 15,0% 10,0% 5,0% 0,0% First attempt to create the pivot table How to create a pivot table? A short tutorial can be found on https://exceljet.net/lessons/how-to-quickly-create-a-pivot-table. Create the pivot table on the sheet OrdersSmall (or on a new sheet if you wish). The result: (the order can be different) Do we need the sum of Margin pct? Or the average? By looking at the data itself, you will understand what is happening in the calculation. Look at "Tube Socks". 10  Sum is not the right way to summarize these margins. Three products with margins ranging from 0.14 to 0.19, have together (on average), a margin between 0.14 and 0.19. 0.16 of 0.17 would be good guesses. Taking the "average" would be better than taking the sum. Use the format area: Change sum to average. The result: Sorting on margin will give a clearer view: Now we see at the top the high-margin products. 11 We can visualize this easily: choose insert / pivot chart. Sales margin per Product 25,0% 23,3% 19,1% 19,0% 19,0% 19,0% 19,0% 18,1% 20,0% 17,6% 15,9% 14,7% 13,6% 13,5% 15,0% 10,0% 5,0% 0,0% This can be the final result. (Remark: The calculation of the average is not completely correct, but is a fair approximation. A more exact calculation will be presented below, while using PowerPivot.) This is a valuable part of a management report. We started from a spreadsheet with a lot of confusing numbers (too many numbers) and here we have a graph that gives a clear overview. Save 002_A_your_name_first_name_classgroup.xlsx Post it on Canvas. The user story (conclusions you can share with your colleagues) A story can contain for instance: "Game over T-Shirts" are sold at a high margin… some reasons why…. There is a category selling at medium-high margins such as Rasta WTC, … some reasons why… There are the low margin products such as Squash Shorts … some reasons why. We will now brainstorm about the possibilities to enlarge our margins… This concludes the exercise. 12 Extra (similar) exercise As we did this exercise on a small example, we should now repeat it on the bigger one. Open 003_A_data.xlsx from starter kit 1 – workbooks part1_A Save as Save 003_A_your_name_first_name_classgroup.xlsx Repeat all the steps we did with the previous demo. It is important to get used to working with large spreadsheets, because many companies have a lot of them. Focus on the step “attempt to create the pivot table”. The result is: (no solution is available. Just compare with the screenshot below. Order by margin %.)... Save 003_A_your_name_first_name_classgroup.xlsx Post it on Canvas. Looking at this graph, we feel that this is not yet the perfect way to look at the data. There are "too many bars" in the graph. Later on in the course we will explore more powerful techniques to visualize in a better way. The commercial importance of the "margin (pct)". How to create a pivot table/chart + customization. The difference between Sum and Average. 13 B Creation of a pivot table linked to a data source EXPLANATION: WHAT IS A LINK TO A SOURCE? The concept is the following: In your company, on some shared network drive, there is a file with the data you want to use. You can copy that data to your workbook and start creating a report (pivot table...). The result will be fine, but one month later, you will have to repeat those actions: you have to copy again the data to your worksheet and maybe you will have to recreate the graphs. Would it be possible to work like this: You copy/link only once to the data source from a shared drive and you create only once tables and graphs. And each month you give 1 single click on a "refresh button" to update your tables/graphs. The most recent data appears in the report automatically. Wouldn't that be convenient? We can draw that concept as follows: Here we show 2 links. The upper one delivers Customer data and also Sales data. The lower one delivers the data of the employees (that closed the sales transactions). In this first exercise we will only create/use the upper link. ACTIONS Create a new excel workbook 004_A_your_name_first_name_classgroup.xlsx 1. Import/link the data sources as follows: Use PowerPivot/Manage 14  Use "From Other Sources" Scroll down to "excel" Browse to the folder "data sources_A" you downloaded from Canvas. Select sales_data.xlsx. Check Test. Next. Check BOTH tables. Finish. You can see the customers: At the bottom of the screen you can switch to orders: The orders (= sales transactions): The link to the data sources is established.. Close the model. 2. We will use the imported/linked data to create a pivot table. You see an empty sheet now, but the linked data is still invisibly present (in the computer memory and in the excel file on your hard disk/USB stick). Insert a pivot table: OK. The pivot table is inserted. The layout resembles: (this a copy from the PPT presentation) 15 Check the fields as shown at the right. Do this in the right order: - Country - City - Customer (customers belong to a city and a city belongs to a country. This is not the case in all companies, but in this course we assume that this logic is valid.) - Sales - Cost In the pivot table, we see all equal numbers and the order of magnitude cannot be correct. What is wrong? 16 3. We will create the missing relation between the 2 data sources. Check the numbers (in a company you always have a notion of the normal magnitude of the numbers. Here, you see that number are repeated for some unknown reason.) So, something is still wrong! Always remember: far too large numbers are in most cases due to a missing relation between the different data source tables. Indeed, we did not explicitly relate the table Customers and the table Orders. Select PowerPivot / Use drag and drop to relate CustID to CustomerID. The relation is created. This link symbolizes the following: if you have a CustomerID in Orders, you can look up the corresponding Customer in the table Customers. Remark: the arrow does not seem to point at CustID, but that is not important. By double clicking the arrow, you can get an alternative pane that describes the relation. double click This pane is just an alternative way to create/edit a link. Close this pane (OK or Cancel) In the pivot table, the numbers look more normal now. Save 004_A_your_name_first_name_classgroup.xlsx 17  This concludes the exercise. How to create a model, linked to data sources. In an existing workbook, how to edit and rebuild the link to the data sources It is important to use data sources instead of copying data, because otherwise, the report has to be rebuild every month. The schema on the next page explains in more detail how excel uses the relations that are defined in the model. 18 EXPLANATION: HOW TABLES ARE LINKED ORDERS CUSTOMERS 19 C Pivot table linked to multiple data sources, with extra calculations Here you see the datamodel. Draw (in this word-document, with insert/shapes/arrow) all the relations between the 3 datasources in the picture above. Always remember that this is the first step before you can create the powerpivot. BUSINESS CASE Where does the profit come from? (Countries/cities/customers) Where is the margin percentage high? Where is it low? We need at least a table like this: (draft) ACTIONS Create a new excel workbook 005_A_your_name_first_name_classgroup.xlsx 1. Import/link the data sources: Use PowerPivot/Manage 20 Use "From Other Sources" Scroll down to "excel" Browse to the folder "data sources_A" from starter kit 1 you downloaded from Canvas. There you will find employee_data.xlsx and sales_data.xlsx. Link to both files, like we did in the previous exercise. While importing you see: During the import, you can get column names like these: The names F1, F2,..., indicate that you forgot to check. In that case, it takes some steps to fix this problem: Top right on the screen, you see Check Diagram View: Delete the tables with names F1, F2,... Do once more the import of sales and customer data. 21  This should result in a model like this: Drag and drop Orders in a central position (drag it to the middle). Notice that we put Orders in a central position, because each order is linked, at the left, to an employee that closed the selling transaction and, at the right, to the customer that bought the product. Though, this logic is not yet defined in the workbook. That will be the next step. 2. Create relationships between the data source tables. We can drag and drop to create the relationship between Orders and Employees, or we can use a right click + Create Relationship. Here we are indicating that Orders/EmployeeID corresponds to Employee/EmpID. E.g. every value "1" of Orders/EmployeeID, refers to a corresponding row in Employee/EmpID where EmpID has value "1". If this seems a little abstract: in the data, you can also see that logic: 22 As a result of the creation of the relationship, the relation appears in the diagram: Similarly, we can relate Orders to Customers. resulting in: The position of the arrows does not matter. If you want to check which fields exactly are linked: double-click: Attention! You can relate the wrong fields. If you connect for instance EmployeeID with CustID then all you reports will be wrong. All the relations are established. So far, so good! Close the task. Now we can build a report in the spreadsheet itself. The sheet looks empty. It looks like the data has disappeared. But the model is still available: by use of PowerPivot/Manage, you can always review the model. because the relations we drew can still feel a little mysterious, look at the schema at the next page, showing how a relationship is used. 23 We are halfway. The data model is in place, now we will create the pivot table and calculate some extra measures (margin and avg quantity). Save 005_A_your_name_first_name_classgroup.xlsx You can compare with the partial solution " 005_A_relations in place ". 3. In case you would use the partial solution "005_A_relations in place.xslx", act as follows: Re-link the data sources. The model of your teacher points to the sources in a folder on HIS/HER computer and you have to make it point to a folder on YOUR computer: PP (PowerPivot) / Manage / select a data source / Edit Browse to the source on YOUR computer Repeat these steps for each data source. Check “Diagram View” to be sure that the relations still exist. Use Manage / Refresh to check the link to the sources! 4. Create the pivot table. Choose tab "tables" (of our model). You can select any table, as the other linked tables will be selected automatically. 24 Even if you selected only one table, using the tab "ALL" shows you all linked tables. Select CustomerCountry and then Customer. (order matters) This results in If you selected in the wrong order you get: The order can be changed Remark: the customers are in the layout panel at the shops, selling our products. lower right. Drag and drop to change the order. 25 Select Sales. The result: Now we will add the level City: By use of you can expand or collapse. But if you want to expand/collapse all the data in one move, use or. Check out the possibilities. Create the reports below with a minimum of clicks, by using Expand/Collapse. 26 5. Add "Margin" to the pivot table. We already discussed the meaning and importance of the margin, and we already learned how to create that column in an ordinary spreadsheet without data sources. Now we will create margin based on the data sources as follows: First create the pivot table and put "Sales" and "Cost" in the table. That way, the [Sum of Sales] and the [Sum of Cost] will be pre-calculated in PowerPivot, as you can see below. Use PowerPivot/"Measures"/New... Use the dropbox to select table Orders (not Employees as shown above). This doesn't make any difference, but we will calculate on numbers of Orders, so it is more “natural” to put the calculation there. and type in "Measure name" the name "Margin". Type = [... and a dropdown list with "Sum of Sales" will appear. Select it. Go on and type a minus sign: -... and use [ to get once again the dropdown list. Select "Sum of Cost". Result: 27  The field "Margin" appears in the field list of the pivot table. ("fx Margin") The margin can be used, for instance as follows: 6. Add "Margin %" to the table. In most management reports, margins are also – additionally - expressed as percentages. (A margin % can be calculated as well per product as per customer. Here we calculate it per customer.) Create "Margin %" and put it in the pivot table: Take the same steps as mentioned above. The formula is: This is the correct way to calculate a margin (better than the calculation with AVG we made in the ordinary pivot table in the beginning of the course). Result: Here we can explicitly show how a margin % is calculated. Let’s zoom in on the row The “sum of sales” for the customer Los Espadrillos Fantasticos is 6923.1 The “sum of costs” for Espadrillos Fantasticos is 6119.74 Margin % = for all sales to Los Espadrillos Fantasticos: (6923.1 - 6119.74) / 6923.1 = 0.116 = 11.6% 7. Do some formatting: Rename "Sum of Sales" to "Sales" Use : 28  Do the same for Costs. Result: 8. The management asks to add "average quantity" between Costs and Margin. This field can be created as already shown above: We will show a slightly different way to do the calculation: Choose. Select on sheet Orders a cell below the separation line: => rename 29  The new measure is Result: available: Save 005_A_your_name_first_name_classgroup.xlsx USER STORY What kind of conclusions can be drawn? By sorting on Margin, we can see where we get the profit from. 30 By sorting on Margin %, the sales representatives can compare the margin on a geographical basis. In a company, such a table is a start for brainstorming/discussions about profitability, reorganizations, business incentives, promotion campaigns,.... Of course, graphs would be more convenient to interpret. linking to multiple data sources calculating new fields (e.g. a margin percentage) formulas such as: use ALT-TAB to browse through open applications. QUALITY CHECK OF THE REPORT: Criteria Are the numbers correct? Can we draw conclusions from the data? Is it easy to use? Check of the criteria Are the number correct? → are they updated? Use refresh to update. Is the update function working? Further, in a real life situation, you should always compare the report to the reports delivered by the accountants. Can we draw conclusions from the data? This depends on what we are looking for. If there is a focus on profitability (sales, costs, margin, 31  margin pct, evolution of these figures through time,...) then we miss some elements. Especially the time dimension should be added. Via "years, quarters, months" the evolution of the financials can be revealed. Is it easy to use? o It's too long. 180 lines (entire field expanded) are not easy to read. o Many managers are only interested in a few countries (the ones they are responsible for). It's not easy enough to collapse the countries you don't want to study. o We should add graphs to enhance the visual experience. What you have learned until here is very valuable in professional life. Excel will be your best friend! 32 D Creation and use of hierarchies Goal of the action: build the hierarchy "Country / City / Customer". ACTION Start from a copy of 005_A_relations in place.xlsx from starter kit 1 – workbooks part1_A Save as 006_A_your_name_first_name_classgroup.xlsx Use the PowerPivot / Manage / Diagram view Drag CustomerCountry onto Geography: The same for CustomerCity and Customer. Drag each time the field onto or under the word Geography. Close the pane. This order "Country => City => Customer" is for this specific organization (our wholesaler) the natural structure of the data. With "natural structure", we mean: each customers (shops) is located in 1 city, and there is no shop with 2 outlets in 2 different cities. So, we assume explicitly that a shop is located in 1 city. This is an assumption, and maybe in the company you will be working for, the structure can be different. (e.g. dealing with big customers, such as a fashion outlet chain, is more complicated) A second assumption is easy to understand: a city belongs to 1 country. Create a pivot table. Use the Geography dimension: check Geography. 33 Also check "sales" and "cost". Explore the drill up and drill down. Use the tab analyze: Use the icons: Experience how easy it is to navigate! Add a "slicer", which makes it easy to select/unselect specific countries. Right click on CustomerCountry: (Alternatively, you could use the icon "insert slicer") By clicking on the slicer, you can (un)select a country. Ctrl/click for selection of several countries. Use to reset the filter. We can also create a filter (which resembles a slicer, but has slightly different functionality) Drag the field CustomerCountry to "FILTERS" at the bottom right. At the top of the pivot table appears a filter: Explore how the filter works. Create a column "avg QTY" and "Margin %". (measures) You know how to do this... 34 Create a pivot chart Uncheck (temporarily) the fields we will not put in the chart. Keep only Sales, Costs and Margin. (uncheck Margin pct and average qty) ANALYZE / Pivot chart: Is this a good choice? No. Why not? Well let's look what happens if we choose this layout. At best we get something like: The country names would be better readable when we turn 90 degrees. And it's more handy to scroll down than to scroll to the right. So choose this layout: 35 Order the table, and the chart will follow. Result: Layout can be customized by a single click on a bar + Format Data Point. Don't put too much time in layout. The numbers and your story (the interesting findings you tell to your colleagues during a presentation) are more important than layout. The geographical hierarchy we made is very handy: double click the green bar of Germany: the chart shows the lower level (cities) of Germany. 36 Learn to use DESIGN / Drill Down/Up Drill down on Finland: Drill up. Drill down on Spain 37 Quality check of the report: Are the number correct? → unchanged Can we draw conclusions from the data? → The pivot chart adds a lot of possibilities! Is it easy to use? → Yes! Save 006_A_your_name_first_name_classgroup.xlsx Upload on Canvas. 38 E How to use a time dimension WHY DO WE NEED THOSE DIMENSIONS AND GRAPHS Tables and graphs are of great importance because "Profit" is always important for a company "Quality" is important and more and more companies try to measure it. E.g. in a factory: o How many goods are damaged before they reach the end of the production line? o How many products are returned by customers? o How many complaints do we get from customers? "Efficiency" is a point of interest. E.g. how many times and for how long has the production line been delayed? PROBLEM TO BE SOLVED: HOW CAN WE SHOW AN EVOLUTION THROUGH TIME In many cases, professionals want to measure whether "the situation" (of profit, costs, quality, public health...) is changing in the right direction. Is profit growing? Is the quality stable or improving? We want to show a table/graph with the different years next to each other. ACTION Start from 005_A_relations in place.xlsx from starter kit 1 – workbooks part1_A Save as 007_A_your_name_first_name_classgroup.xlsx Check PowerPivot / diagram view. Is there any calendar present? No. Do we see dates in the sales records? Yes!. To report sales per period, we first need a calendar table. This is just a table with all the dates of the year and of the previous years, supplemented with some extra columns such as month, quarter, … For your convenience, the table calendar is already present in the folder “data sources”. Open the calendar. You notice: Date: the 0:00 is midnight, but the exact time does not matter. 1 day = 1 row. All dates are listed. Weekday numbering: 1 = Sunday. In the upper left, use. “datetable” shows. 39 Select “datetable”. The selection of all dates turns blue. This area “datetable” has been defined by your teacher, in order to give a name to the complete range of dates. Close the workbook calendar. We will now integrate the calendar in the data sources. (= import of the table and establish the relation between calendar and sales_data) Do this as follows: Open PowerPivot / , select Check the datetable (“datetable” is the “range” in the calendar.xlsx wherein the dates are contained.) You can see now at the bottom of your screen that the calendar has been added to the model. Sort on Date. Now we will create the relationship between OrderDate (table Orders) and Date (datetable). (Without the datetable, the structure “year, quarter, month” would not be present in the model and could not be used.) Select. You will see that the calendar (datetable) is not yet attached to other tables.. 40 Create the relationship between Date and OrderDate by drag and drop: Because OrderDate is attached to the calendar, a "sum of sales" for 1 particular year will be calculated by adding all the sales with OrderDate in that year. E.g. "sum of sales for the year 2022" will be a sum of all sales with OrderDate in 2022. The calendar (datetable) should always be linked to the table containing the facts, such as sales, costs and other numbers. Create a pivot table, showing sales (and a graph of sales). It should be possible to focus on 1 single year by use of a slicer. In the pivot table (tab ALL) you can see the date dimension ("datetable") is available. Create the pivot table as usual (CustomerCountry,...Sales). Test the slicer with 2019. Create the chart: Watch how the chart changes when you select other slices. We are "slicing" the time. Each slice is 1 year. Can you zoom in on quarters or months? (No, but we will solve that in the next paragraphs.) 41 Create a new sheet in the workbook. Create on this new sheet a new, supplementary pivot table showing the evolution of the Margin. Show a hierarchy of "Country, City, Customer" and a hierarchy of "Year, Quarter, Month". Also add slicers for Country and for Years. First create the column "Margin". In previous part of the course, we calculated Margin in the way it is done in excel. But now, we have to calculate in the model, containing the data sources. This goes as follows: PowerPivot / manage / Orders. Rename to Margin. Build in the upper cell a formula as follows: (similar to the way you would do it in excel) Look at the special syntax that shows while clicking: [Sales] - [Cost]. By clicking, you don't have to type it. And the whole column is filled automatically. This is logical, because in a BI-tool, calculations act on a whole file, often a very large file. Stretching down a formula is not an option when you have 1.000.000 rows or more. Here, in PowerPivot, the software does this for you. The result: and all other rows are filled too. Why don't we use here the PowerPivot "measures"? "measures" are always SUMs, AVERAGEs...acting on several rows at the same time. In other words, “measures" aggregate (=summarize) data of many rows. In this case, our calculation is not an aggregation, but a row calculation: it is an action within a single row (and this action is applied on each row separately). In such case we do not use a measure. We just add a column (Margin) to store the calculation and the result. Create the hierarchies (as shown in part 2 of this course). If some of the shown hierarchies is already present: delete it first. 42 Create the pivot table and 2 slicers You already did this in the previous exercise. In this screenshot, 2021 and 2022 are selected. All countries are selected. To sort all data by "margin of 2022" there are multiple possibilities, such as: click in the column you want to sort and use "Sort". click on one of the colored bars of the graph and use "Sort". Unfortunately, when the highest figures are on top in the table, the longest bars are at the bottom. This reversed relation cannot be avoided (unless you apply some tricks as we will show later on). Remove grand total: right click on the table. "Remove grand total". WHAT CAN WE DO WITH THIS GRAPH? On a meeting with the sales managers of the different countries, it can be useful to talk about the evolution of the profit between the 2 most recent years. The managers will notice for instance: Germany has a very high margin (but this is a big country). The margin grows slowly. France is doing a lot better than last year... Brazil also looks promising, because of the growth. In the US the alarm bell is ringing! There is a decline to be explained.... Of course, many more graphs are needed and many influences have to be taken into consideration (e.g. the evolution of the US economy is not parallel to that of Europe). ACTION Suppose you are the general manager. How will you analyze the graph above? You would be looking for remarkable changes in margin. As well the favorable changes as the unfavorable ones. Try to answer the following questions: (and try to experience the logic in this sequence of questions) 1. Is the company as a whole doing well? Estimate the growth of the margin. 2. In which countries is the margin growing remarkably? a. Zoom in on France, by using drill down. In which cities is there a growth? Are there some customers with no growth? (Use "expand entire field") Use drill up to go back to the list of countries. b. Zoom in on Denmark and answer the same questions. c.... 43  3. In which countries is the margin shrinking? a. Zoom in the USA and draw conclusions. Look for the handiest way of zooming in/out. Try using drill up/drill down in the pivot table, try using expand/collapse, try combining with "back" (on top left of the screen), try clicking in the graph itself. b.... This table and graph still have some drawbacks: A few months later, a similar report will be asked, with the years shifted by 1 quarter. Otherwise, the report would not be up to date any more. Both year periods should be shifted to make them start at April 1st and end on March 31 of the next year. In the report above the periods (years) cannot be shifted by a quarter, because the years are fixed. Some managers would like to see the growth between years (and between quarters) as a percentage. Adding that percentage would be useful. But how can we calculate this? Save 007_A_your_name_first_name_classgroup.xlsx 44 F YEARS, QUARTERS, MONTHS and RUNNING TOTALS Definition of YTD, QTD, MTD Many companies use tables and graphs with time periods as shown below. Test yourself! Now = 09/09/20xx (xx = this year). From when to when goes YTD? Answer: 01/01/20xx - 09/09/20xx Now = 21/10/20xx (xx = this year). From when to when goes QTD? Answer: 01/10/20xx - 21/10/20xx Now = 21/10/20xx (xx = this year). From when to when goes MTD? Answer: 01/10/20xx - 21/10/20xx Now = 30/07/20xx (xx = this year). From when to when goes YTD? Answer: 01/01/20xx - 30/07/20xx Now = 30/07/20xx (xx = this year). From when to when goes QTD? Answer: 01/07/20xx - 30/07/20xx Now = 09/09/20xx (xx = this year). From when to when goes "prior YTD"? Answer: 01/01/(20xx-1) - 09/09/(20xx-1) Now = 21/12/20xx (xx = this year). From when to when goes "prior QTD"? Answer: 01/07/20xx - 21/09/20xx Now = 30/07/20xx (xx = this year). From when to when goes "prior YTD"? Answer: 01/01/(20xx-1) - 30/07/(20xx-1) How many of the answers given above are wrong? 45 Using YTD, QTD, MTD In the company/organization where you will be employed in a few years, you always have the opportunity to use all sorts of "documentation". So, there is no need to memorize complicated formulas. In this course and during the exam, the following formulas will be available for use: TABLE OF FORMULAS YTD Sales:= TOTALYTD([Sum of Sales];Calendar[Date]) QTD Sales:= TOTALQTD([Sum of Sales];Calendar[Date]) MTD Sales:= TOTALMTD([Sum of Sales];Calendar[Date]) Sales previous Month:= Calculate([Sum of Sales]; PREVIOUSMONTH('Calendar'[Date])) Compare previous month:= [Sum of Sales]-[Sales previous Month] Growth previous month:=([Sum of Sales]-[Sales previous Month])/ [Sales previous Month] Month Sales Last Year:= Calculate([Sum of Sales]; PARALLELPERIOD('Calendar'[Date];- 12;Month)) PARALLELPERIOD('Calendar'[Date];-12; Month)) => Returns the month, 12 months before the specified day Compare parallel month:=[Sum of Sales]-[Month Sales Last Year] Growth parallel month:= [Compare parallel month]/[Month Sales Last Year] You have to learn how to adapt these formulas to the situation you are dealing with. E.g. renaming "calendar" to "datetable" (or other names!!), changing [Sum of Sales] to [Sum of Cost], using other time periods,... 46 Next, we will learn how to use these formulas in a series of exercises. BUSINESS CASE Show in a graph the sales of 2022, month by month. Show how these sales figures accumulate month by month. (This is a technical exercise, to get used to using formulas. Few business conclusions can be drawn from this graph.) ACTION Start from 007_A_calender_in_place.xlsx from starter kit 2 Create a measure. You already learned how to do this. If needed, go back in the course and look it up. From now on, the abbreviation "YYC" will be used to indicate that you can solve the problem yourself. Start creating a measure: Use the table of formulas and select the formula that is fit for the purpose. The right choice is: "YTD Sales:= TOTALYTD([Sum of Sales];Calendar[Date])" We only have to change the name of the calendar. In our model it is called Datetable. 47 We can copy/paste the formula from some example, or we can start typing:. Select TOTALYTD. Type [.. Select Sum of Sales. Type; Type D (of Datetable). Complete the wizard TOTALYTD([Sum of Sales];datetable[Date]) Go to the pivot table and tick the measure. And after some clicking, you will reach the aforementioned goal. Save as 008_A_your_name_first_name_classgroup.xlsx 48 EXTRA EXERCISE Start again from 007_A_calender_in_place.xlsx (or with the result of the previous exercise) BUSINESS CASE Create a graph, similar to the previous one, but this time, based on Cost. Save as 009_A_your_name_first_name_classgroup.xlsx AND ANOTHER EXTRA EXERCISE Start again from 007_A_calender_in_place.xlsx (or with the result of the previous exercise) BUSINESS CASE Create a graph, similar to the previous one, but this time, based on Margin. Start by creating a new column "Margin". Save as 010_A_your_name_first_name_classgroup.xlsx 49 BUSINESS CASE Compare in a graph the sales of 2021, month by month. Show sales, sales previous month, growth and growth % in a table and on a chart. (Of course "evolution" is a more appropriate concept than "growth", because "evolution" is about highs and lows, whereas "growth" suggests only positive growth. But managers like to present the future as a story of growth, not of evolution.) ACTION Start again from 007_A_calender_in_place.xlsx (or with the result of the previous exercise) Create the pivot table: Look for the formula you need: Choose: Calculate([Sum of Sales]; PREVIOUSMONTH('Calendar'[Date])) Create a measure: =calculate([Sum of Sales]; PREVIOUSMONTH( type ' =calculate([Sum of Sales]; PREVIOUSMONTH(datetable[Date])) 50 Add the field in the pivot table: Create another measure "Compare previous month". In the table of formulas you find: [Sum of Sales]-[Sales previous Month]. And create once more a measure "Growth previous month". Use the table of formulas. Try to get the layout below: 51 OK, we have created the table. Let's evaluate how useful it is. Evaluation of the table By use of such a table, managers (and assistants) want to get an idea how sales evolved from month to month. Is this table handy to look at the evolution? Always try to show numbers that are understandable correct interesting to know Can you suggest some changes that would make the table better? Some problems to solve: Some numbers are not easy to interpret, especially those on top. E.g. 84.332 shows twice?!?There is a repetition: bottom = top. At least one percentage is very big. What's wrong about this? (answer: after a month with few sales, the next month will show a huge growth.) Why do we consider these items to be "problems"? Answer: tables and graphs have to be used by people who have no time to waste. So, there has to be a "story" in the numbers, something that is useful for managing the business. From this point of view, it is obvious people don't want tables with confusing numbers that are difficult to understand or numbers that are repeated without a good reason,... ACTION To make the table better readable, take following actions: Unselect "previous month sales" (at the right of your screen, uncheck in the field list) Unselect "compare previous month sales" (uncheck in the field list)Remove grand total (right click grand total. Remove grand total.) This yields a table that is easier to read: We will not create a graph now. The reason is: sales have been very unstable. Graphing the growth percentages would not lead to a better insight. Actually, month are too short to consider. It would be better to use longer periods: years or at least quarters. Save as 011_A_your_name_first_name_classgroup.xlsx 52 BUSINESS CASE Show the evolution of sales of 2021 and 2022, quarter by quarter Show sales, growth % in a table and on a chart. ACTION Start again from 007_A_calender_in_place.xlsx (in starter kit 2 or with the result of the previous exercise) Look for the formula you need. Starting from "Sales previous Month:= Calculate([Sum of Sales]; PREVIOUSMONTH('Calendar'[Date]))" we can create the formula we need: "Sales previous Quarter:= Calculate([Sum of Sales]; PREVIOUSQUARTER('Calendar'[Date]))" Create a measure "Sales previous Quarter" Adapt the following formulas: Compare previous month:= [Sum of Sales]-[Sales previous Month] Growth previous month:= ([Sum of Sales]-[Sales previous Month])/ [Sales previous Month] What we need is: Compare previous quarter:= [Sum of Sales]-[Sales previous Quarter] Growth previous quarter:= ([Sum of Sales]-[Sales previous Quarter])/ [Sales previous Quarter] 53 Create a measure "Compare previous quarter" and "Growth previous quarter" Create a table and a graph: What has to be changed to get a good table and a good graph? Open your eyes and start up your brain! To create something good, you must focus, and ask yourself "'what will my client (manager, colleagues) think when they see this?" We could make these changes: omit grand total omit the percentages on the level of the years make the growth show on the graph. Do this as follows: Click op 2021. Uncheck Subtotal... Click on Grant Total. Check Remove... 54 Click (twice) in the chart on. Right click. Check Right click the red series.. This results in a strange layout. Of course we don't expect our audience to like this. So, we will try to improve it. Personally, I would expect to find a solution in "format data series". But it seems the only solution is to be found in change series chart type. Change the red series from to. This yields: This readable, although, personally, I would prefer to omit the red curve. If we keep the red curve, we should clearly show that some percentages are negative. Unfortunately, pivot tables/graphs are not easy to format. And if we manage to format them, they could lose the format when new data arrives. Thus, let's remove the red line. Do this as follows: Select the table (completely). Paste the table at the right of the graph. Use the filter to check which table is linked to the graph. As you can see, the original one, at the left is linked. 55 Uncheck "growth previous quarter". Use drag and drop to switch position of both tables. Now, you have one table at the left, to show to you audience and one table (at the right) to manipulate the chart. This what you should show on a presentation. Save as 012_A_your_name_first_name_classgroup.xlsx The meaning of the special time periods such as YTD, QTD, previous month, previous quarter,... How to calculate growth How to customize a pivot table and a pivot chart 56  4 Parallel periods Many companies use tables and graphs with time periods as shown below. TABLE OF FORMULAS (extract) YTD Sales:= TOTALYTD([Sum of Sales];Calendar[Date]) QTD Sales:= TOTALQTD([Sum of Sales];Calendar[Date]) Month Sales Last Year:= Calculate([Sum of Sales]; PARALLELPERIOD('Calendar'[Date];-12;Month)) PARALLELPERIOD('Calendar'[Date];-12; Month)) => Returns the month, 12 months before the specified day Compare parallel month:=[Sum of Sales]-[Month Sales Last Year] Growth parallel month:= [Compare parallel month]/[Month Sales Last Year] BUSINESS CASE 1 Compare the sales of the quarters of 2022 with those of 2021. As the business is about clothes and shoes, sales go up and down according to the seasons. Therefore, 2022 Q1 should be compared with 2021 Q1. 2022 Q2 with 2021 Q2 and so on. The manager and the assistants want to be able to compare sales of these parallel quarters compare sales of these parallel quarters per employee (to evaluate the performance or workload of the individual employees)Create a layout as shown here: 57 Comparing 3 years would also be interesting because this reveals even better the evolution of sales. ACTION Start (once again) from 007_A_calender_in_place.xlsx from starter kit 2 We already know how to create measures. The following calculations are needed: As you can see, here we use "PARALLELPERIOD", (Using "sum of sales" instead of this formula would give whereas at the left, we use "TOTALQTD". the same result.) Now it is easy to create the following table: 58 To get a better layout, use: To compare 3 years, create one more field: Create the following table: Use the slicer to evaluate the employees. Which employee is evolving positively? Which one evolves negatively? If you really try to evaluate the performance of the employees, you will encounter some problems: Wouldn't it be useful to compare the "year totals" also? Or in other words: wouldn't it be logical to start by comparing years and comparing quarters afterwards? (Going from a general view to a more detailed view.) We can look in the menus of PowerPivot how to incorporate the year totals (Q1, Q2, Q3, Q4, Total_of_the_year). Unfortunately, no such option seems to exist. Also Mr. Google will admit that it doesn't exist (yet). Thus, we have to be creative. Look at the action we will take. 59 ACTION Add a second pivot table and a second graph with an overview based on years. Create this additional table and graph: Rearrange your report to obtain the handy layout shown below. Here you see a screenshot filtered on Elvis. (He lives?! ) The slicer should be connected to both tables and both charts. You can learn how to do this in the video: https://exceljet.net/tips/how-to-use-a-slicer-for-multiple-pivot-tables Experience how well this reports works! Save as 013_A_your_name_first_name_classgroup.xlsx 60 EXTRA EXERCISE 1. Start again from 007_A_calender_in_place.xlsx (from starter kit 2 or with the result of the previous exercise) Create a graph, similar to the previous one, but this time, based on Margin. create in the PP/Manage/table Orders a new column Margin. 2. Then, show the margin in your pivot table (even if you will omit it later). This way, PP will activate this new field. 3. Because of your action in point 2, you will be able to see the field Margin when you want to use it in a calculation. E.g. Save as 014_A_your_name_first_name_classgroup.xlsx What is the difference between PREVIOUSYEAR and PARALLELPERIOD? How to choose between those two? PREVIOUSYEAR refers to exactly 1 year (12 months) PREVIOUSQUARTER refers to exactly 1 quarter (3 months) PARALLELPERIOD refers to a period that can be longer or shorter, depending on the selected part of the timeline. Do it like this: First, define a timeline (before creating previous or parallel periods) Select some recent period on the timeline If possible, use the function PARALLELPERIOD. This function is in most cases more useful than the PREVIOUS… What to do if the whole column (e.g. sales previous period) is blank (empty)? Probably, you didn’t define a specific recent period and excel will consider the whole period covering all the data. (What happens then: the function parallelperiod or previousyear then try to select data earlier in time than all the existing data. But nothing is earlier than all the existing data, thus the column remains empty.) Solution: create timeline and select a recent period. The blank column will show the data. 61 BUSINESS CASE 2 In chapter 3.B you learned how to create a model, based on links to external data in excel. But external data can also reside on a remote server. It can be a database, a text file,... Here, you will learn how to connect to an access-database. An access-database is very similar to other databases, thus the way to connect always looks the same. You will recognize this in the company where you will work in a few years. ACTION Create a new workbook. Rename it to "Orders and customers from access database.xlsx". Open Orders and customers.accdb from Starter kit 2. Double click the database and look at the data. Close the database. Open the new workbook. "Orders and customers from access database.xlsx" Import data as follows: Remark: don't think that all menu's in excel lead to the same result. The import is in the "Powerpivot Manage menu". Not the standard excel menu that looks similar: This menu is also useful, but cannot import data in a model. In Powerpivot, we want to get the data in the data model. Therefore we use the PP menus: 62 Use the wizard to go to the database. Use the correct path on your computer. Tick the tables. Finish. 63 Check your result with the picture above. In data view, you can see the data. In diagram view, you can even see that the relations are already in place, because these relations also existed in access. (If they are not present in access, then the relations have to be created in Powerpivot.) In fact the datamodel could be completed by adding a table "products". These are mentioned in the table Orders, but it can be more convenient to have products as a separate table. We will now add products as a separate table. It is delivered by means of the text file "products.txt”. Look in Starter kit 2 and import it into the model. Open products.txt and check it. It seems OK. Close. Use again: Now use (scroll down)... Use semicolon. 64 The data arrives in the model. In this case we cannot expect the data to be linked automatically to the existing tables. We have to draw an arrow. We will draw FROM THE INSIDE TO THE OUTSIDE. This means: for each order, the ProductID in Orders can be found once and only once in the table Product. There ProductID is the UNIQUE number of the product. In Orders, a ProductID e.g. 29 can occur many times, as often as there are sales transactions in the table Orders. In Products, each product is mentioned exactly once, as this is the unique list of products. Product 29 is exactly 1 row in the table Products. What can go wrong? You can get this error: In that case, as the message indicates, there are duplicate values, meaning that the outer table - products in this case - does not contain 1 row per product. There is at least one row that is duplicated. To solve that issue, you have to recreate/change the outer table and make sure every row is unique. (not mentioning products twice). But, in Powerpivot, we always use a model that is imported from and linked to external data sources. Thus, the source(s) should be corrected. In this case, the source products.txt should be repaired/corrected/cleaned. 65 When that action is finished, in the sheet "Orders and customers from access database.xlsx" the data can be refreshed, and the table products can be integrated in the model. The model finally looks like this: For instance this report can be created: (do so !) Save as 014_B_your_name_first_name_classgroup.xlsx 66 5 Using Query INTRODUCTION With Query, you can E read (EXTRACT) the data from all kinds of data sources. The ICT department knows where the sources can be found, and so do your senior colleagues. Ask them. T change (TRANSFORM) and clean the data L LOAD the data into your model (Excel, PowerPivot) ICT-professionals call this "ETL". You are already familiar with the following schema's: A flow from left to right (sequence of Query, PowerPivot, Power View and Power Map) You can see the flow Files → Query (extraction of data + changing data + loading into the model) → PowerPivot has the model and pivot tables can be created → Power View and Power Map can create even more reports, graphs and maps. A flow from top to bottom, showing Power query Here at the bottom, you see the icon of the data model of PowerPivot. From there, you create the pivot tables. The most important reason to use all this software, is the fact you can handle data yourself. The ICT- department doesn't have the time to help you all the time, and therefore self-service-BI is important. 67 EXCERCISE (GETTING FAMILIAR WITH POWER QUERY) Download from Canvas once again: 007_A_calendar_in_place (do a fresh download) starter kit_3: employee initials We could import the employee initials directly into power pivot, but we want to learn how to use power query. Open 007_A_calendar_in_place. Save as 015_A_Name_Firstname_Classgroup.xlsx. Open file 007. Click Data / new query / From file / From workbook. Select. Load. (this loads the spreadsheet into your workbook). Now you can see even better what data you have got. Scroll, look at it. As we want the data in our data model, we will load again. Do the same again: From file: From workbook / Select. , but now: 68 Now the data is added to the Model in PowerPivot. Go to PowerPivot/Manage and there you find: Link the data. (YYC!) But, in fact we loaded times. The first load resulted in a worksheet and the second load resulted in a data table in the PP Model. We want to do it right the first time. Close Power pivot. Go to Data / show query. Delete everything in the menu. Do again: From file: From workbook. Select. , but now: with the following option: Double click at the right. The detail open and you see: 69  Click on Source.. Click on the little wheel:. And there you can see where the data comes from. Cancel. + indicates which part you selected in the source workbook. means: the first row has been considered as column labels. : the meaning of this step remains unclear. Close the dialogue. The steps above are steps query does for you automatically. And it will perform these steps every time you click "refresh". (at the right) Try and click it. From now on, we will use the abbreviations PP = Power Pivot ; PQ = Query ; PV = Power View ; PM = Power Map Open PP. Link the table with initials. Create a pivot table like this one: Do you see who will get the biggest bonus this year? To finish this exercise, we will change the initials of an employee in the source file. Then, we will see if we can get this change through PQ into the model of PP and finally in our pivot table. 70 ACTION Open the source file "employee initials.xlsx". Change EP to ELVIS. Close the workbook. The question is, how can we refresh the data? Simply, by using PP / Manage / Refresh Refresh. Nothing has changed yet?!? Close, and while you close, the magic will happen. Open 007_A_calendar_in_place. Save as 015_A_Name_Firstname_Classgroup.xlsx. Using PP, or PQ and PQ together, gives you the convenient feature of "updating your data with a few clicks" (refresh). This can save you a lot of time! 71 6 Using a datasource by means of query The management has subdivided the customers, based on the "margin of 2022". In this exercise, we will list the customers, based on this subdivision. The result should look like: ACTION: Get on Canvas in starter kit_3, the workbook "Customer value class.xlsx". Get also a fresh copy of 007_A_... Open Customer value class.xlsx look what is there: Open workbook 007_A... Create the new column "Margin" (Sales - Cost). Use PQ (Query) to add the data to your model. Link the table in the right way. (Tip: all the arrows in the model have to point outward, away from the fact table, Orders.) Insert a pivot table. Tick the fields you need: Year Margin class Customer name Filter Year : select only 2022. Sort on Sum of Margin. Compare with the screenshot on top of this page. Save as 016_A_YourName_Firstname_Classgroup. 72 7 Using Query to change data or to create extra data BUSINESS CASE Your company wants to compare sales and margin for the different products. It would be convenient to create graphs a hierarchy of product and "product group", such as the one shown below. Where can you get a file with "product groups"? As always, if you don't have it, ask the ICT-department. Unfortunately, they don't have the time now to give you a file that matches 100% your needs. But a file that is a slightly too big, can be delivered soon. Alternatively, you could create a file with product groups manually, but then, sooner or later, some other colleagues will use different product groups, as they also define product groups manually. Relying on the corporate data files (corporate definition of the product groups) is the best solution in the long run, because that way, all reports will be based on the same product group definitions. ACTION Use a fresh copy of 007_... Use "product data.txt" Open "Product data.txt" from starter kit 3 by double-clicking. This looks disappointing. But in fact, the group- and subgroup definitions we need are present. We just have to manipulate the data a little bit. By looking closely, you can see the data structure: 3 fields: o ProductNbrName ( number and name together in one field?!?) o Product Subgroup o Product Group. Fields are separated by " ; " ( ICT'ers call the " ; " a delimiter ) 73 Now we will create a flow source => Load data in Power Query and do some manipulations => load to the model in Power Pivot Get (="extract") the data Browse to the text file: by using: The data shows: The row headers are still IN the data. Use the tab : to put the column names on top. Result: There is still a problem: the product number and product name are together in one field. Splitting those two would be convenient. In power pivot, we need product number to create a link in the model. And product name is needed to sort alphabetically. Splitting goes as follows: The result is: Right-click the column labels to rename as follows: Close and load. If the data is loaded as a sheet, right-click the query to find "load to". 74  Load to... model (and only a connection) Look in PP to the model:. By looking at the data, we realize that the product data was not reduced to a list wherein every product occurs only once. We have to add an aggregation in Query. Double-click the query to open it. In this specific case, we see that the data is replicated many times. Use remove duplicates: Sort on ProdNbr. To have an overview of the changes, click the query steps (source, changed type,...) one by one and notice the changes in the data. Close and load.. Now the product data is ready to load to PP. Open the model in PP. Check and recheck the data. Which product has 2 different numbers? (Jack Flash Dress) After consulting the ICT-department, this seems to be an isolated error that will not re-occur. Is the number 153 used in the table Sales? Because it is not, we can just delete the row 153 in the product table. Can we delete it in PP? Let's try to delete it in PQ. You can always use to show the PQ-pane. How can we remove the row with ProdNbr 153? We might, somewhere in the middle of the query steps, change the value 153 to 53. (Of course, this is not the only way to handle the situation and this specific procedure is not ideal, but data manipulation is never easy and it is no exact science.) 75 Select the step just before Remove duplicates. ( ) Next, use. You can see that the number of rows loaded has decreased by 1. Close and load. Check in PP if the number 153 has gone. How should the product table be linked? which fields (columns) should be linked? Why? from where to where should the arrow be drawn? Your answer: If you don't know what to answer: ask the smart classmates that attended class. You can also look at the previous exercise, where the same question came up. Save (intermediately) the workbook as 017_A_YourName_FirstName.xlsx Create the following report: Tip: As we can see, the margin % is rather high for most products. Compare the evolution of the margin with the previous year. 76  Here, you could wonder why we don't write [Sum of Margin %]. First, percentages should not be summed. Second, Margin % is already a calculation in itself. It contains "calculating the sum" as shown in This is similar to the previous calculation. What is your conclusion about these figures? As you can see, margins went down! This a serious situation, calling for action. The managers will have to start acting upon this data. Save as 017_A_YourName_FirstName.xlsx and upload. 77 8 Using KPI's As "sales" and "margin" are very important figures, the CEO wants a strict follow-up. The managers on all levels have to utilize a scorecard to monitor sales and margin for every product(group) and for every employee. Every manager has got the strict order to use at least monthly, the scorecards provided. In this chapter, we will learn how to create scorecards. Put KPI start.xlsx from starter kit 3 on your desktop and open it. Save as 018_A_YourName_FirstName.xlsx. BUSINESS CASE Create scorecards in which we can do a follow-up of the following 3 items: 1. Sales growth 2. Margin % 3. Evolution of margin % We will create several scorecards, in order avoid information overload on one single sheet. You see a screenshot of the score card “sales”. Green = "sales growth >= 20%"; Yellow = " 10%