Figure 1004 Your data is now available for building charts.

Additional Details: If you have an Excel workbook that already has Power Pivot relationships defined, use File, Import, Excel Workbook Contents instead of the above steps.

IN POWER BI DESKTOP, THE POWER PIVOT TOOLS ARE CALLED MODELING

Power Pivot was revolutionary in Excel 2010 because of three things:

●● Define relationships between two tables

●● Add a new measure (or calculated column) to a pivot table using DAX formulas. ●● Add a new column to a table using DAX formulas

Those three tools are available on the Modeling tab in Power BI Desktop.

Figure 1005 The Power Pivot tools are on the Modeling tab.

DEFINE RELATIONSHIPS IN POWER BI DESKTOP

Choose Modeling, Manage Relationships. Power BI already detected the relationship from Sales to Geog- raphy. You have choices to Add, AutoDetect, Edit or Delete a relationship.

Choose Add to create a relationship between the Sales data and the Calendar table.

Open the top drop-down and choose the Sales table. A list of fields will appear. Click on the Date heading to choose the key field in the Sales table.

Open the second drop-down and choose the Calendar table. A list of fields will appear below this drop- down. Click on the heading for the Date column.

Review the settings for Cardinality and Cross Filter Direction. Click OK.

3

Figure 1006 Define a relationship between two tables.

CLASSIFY YOUR GEOGRAPHY FIELDS IN POWER BI DESKTOP

Before you begin building visualizations, take a few minutes to classify fields in your data.

Click on the Geography table in the Fields list to see the fields available in that table. Click on the field for City. In the Modeling tab, open the Data Category drop-down and choose City. Repeat for State.

However, Power BI Desktop sees the Year and Month Number fields as 100% numeric and wants to sum those fields. That does not make sense.

Figure 1010 The Sigma next to MonthNo and Year will cause problems.

Click on the word MonthNo in the above. In the Modeling tab, open the Default Summarization drop-down and choose Do Not Summarize. Repeat for Year.

3

Figure 1011 You don't want to Sum things like Account Number, SKU, or Year.

SORT THIS BY THAT

By default, month names will sort into alphabetic sequence. Click on the Month field in the Fields list. On the Modeling tab, open the Sort by Column and specify that Month should be sorted by Month Number.

Figure 1012 Make sure that month names are sorted by month number.

DEFINE SYNONYMS IN POWER BI DESKTOP

The fake data used in this example has names like Revenue and COGS. Your real data might have less logical names such as lgQtyTimesPrice. You can improve the Q&A experience by defining synonyms. For example, while accountants use terms like COGS, the person who uses your dashboard might call this Cost or Expense or MLO. You will know what terms are used at your company.