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.
- Matt Lavieri
Matt.Lavieri@protiviti.com