​This is Part 3 of a three part series on How to Analyze your SharePoint Data Using Power BI. This series is aimed towards Business Users with a working knowledge of PowerPivot. If you have missed the previous parts follow the links below:

Part 1: Exporting SharePoint Data to Excel

Part 2: Setting up the Data Model

In the previous posts, I covered how to connect SharePoint data to Excel and how to create a data model linking the tables together. In this post, I will cover how to create Hierarchies and Calculated columns within the Data Model.

Creating Hierarchies

Creating a Hierarchy is a great way to visualize data with multiple levels. For example, at one level you can view sales data by each Manager then drill down to see the performance of each Employee that manager oversees. 

To create a Hierarchy navigate to the Diagram View in Data Model. To get to the Data Model select ‘Manage’ under the ‘PowerPivot’ tab in Excel.​ 

In the Diagram View:

1. Right click on the table you would like to create a hierarchy in

2. Select ‘Create Hierarchy’ from the menu

3. Enter a name for your Hierarchy, this can be whatever you want, but I find it helpful to name it the with the columns I will be adding

4. Drag and drop the columns you would like to add into the newly created hierarchy.
                Note: These need to be the order you would like to drill down.

In the end, you should see something like this: 

Manager is in the first position while Employee is in the second, which means Manager will be the top level while Employee will be the second when drilling down.

In your PivotTable you will now see a new option under the table you just created the hierarchy in. 

Selecting the Hierarchy will add those columns to the PivotTable. At this point, the data looks the same as if you added Manager and Employee to the PivotTable normally. However, by creating a hierarchy you can now drill down on this data, instead of just expanding the set. To drill down on the data simply double click on the cell containing the column and you will drill down to the child data. If you are creating a PivotChart with the data, you can also drill down by clicking on the graph.

Hierarchies allow for a cleaner view when trying to drill down on data and allow for great visuals with your SharePoint data.

Calculated Columns

Many times your SharePoint data will only include the part of the equation. For example, you may have sales data in a SharePoint list, but it only includes the number of units and the price per unit. To find the total cost of the sale you will need a calculation. To add a calculated column:

1. Navigate to the ‘Data View’ of the Data Model

2. Scroll to the right end of the table and click ‘Add Column’

3. Enter your calculation into the formula bar and press enter.
                Note: The Data Model uses Data Analysis Expressions (DAX) which has some slight differences from the basic formula bar in Excel however many of the same functions are available to use.

4. The column is now added to the Data Model and can be used the same as the other columns, you can rename the column by right clicking on the title and selecting ‘Rename Column’.  

Conclusion​

You have now connected your SharePoint lists to Excel, Set up a Data Model, created relationships, created hierarchies, and created calculated columns. By now, you should have a basic understanding of the data model and some of the advanced analytic features available in Excel. Remember, these posts have only scratched the service of what is possible in Excel, so if you have a question or are curious if something is possible, or just want to learn more please do not hesitate to reach out.