Welcome back to our Getting Started with Excel and Power BI Series.
In Part I of Getting Started with Power Pivot we created our first Excel Data Model. Today we will create additional relationships and start using DAX to add business logic to our Model.
To follow this tutorial you will need:
After making the data show up using the right relationships in part I, we want improve the Pivot Table as we want to slice my data by year. To do this I’ll add a slicer to my worksheet:
Clicking the slicer doesn’t do anything yet as there again no relationship between the Invoice and DateTable. After inspecting the fields in both tables I find out that there is no field in the Invoice table that I can use to relate with. Luckily we can create a field in the table that allows us to create the relationship.
To work with the Data model In Excel 2013 we use the Power Pivot add-in. This add in is installed by default in many versions of Excel, you just need to enable it.
Now that we have enabled the Power Pivot add in a new ribbon will show up in Excel:
To work with this model I click the “Manage” button, this will open the Power Pivot dialog:
The initial dialog that opens shows each tables in the Model with all the rows of data, in this case it shows the DateTable. You can switch between tables by clicking the table tabs at the bottom:
I now select the Invoice table where I want to create a column that adds a Date value to for each row of data. I do right mouse click on the CalendarYearMonth column and select “Insert Column”.
This creates a calculated column in the Invoice table where I use a formula to populate the column with values, I combine values from the CalendarYearMonth column to create a new date value using the following formula:
=DATE(
LEFT([CalendarYearMonth],4)
, RIGHT([CalendarYearMonth],2)
, 1)
As you might notice the formula used here is very similar to the formula’s you would write in Excel, this on purpose, the DAX language used in Power Pivot is designed to be familiar to the Excel users. It has some differences that are mostly focused around capabilities that are not supported by Excel like traversing relationships and special built-in functionality for calculations.
Adding the calculation to the data model will give us a new column that we can use in the relationship:
I then rename the column to InvoiceDate. To create the relationship I can also move over to the diagram view in the Power Pivot add-in and drag and drop the fields that I want to create a relationship between. Dragging and dropping results in a relationship being created:
If we now go back to our PivotTable and we now see the results we expect:
Now I want to compare the RevenueAmount for the selected year with the RevenueAmount for the previous year. In order for me to do this I can again use a DAX formula. In this case I want to create a calculated field in Excel to add to my PivotTable.
I go to the Power Pivot ribbon and click Calculated Fields, “New Calculated Field”:
This opens a new dialog that allows me add a new DAX formula that leverages some of the special DAX time intelligence formula’s to calculate the “Sum of RevenueAmount“ for the previous year:
[RevenueAmount PreviousYear] =
CALCULATE(
[Sum of RevenueAmount],
PREVIOUSYEAR(DateTable[Date])
)
This will add a new field to the PivotTable that shows the RevenueAmount for the previous year compared to the Year selected in the slicer:
Now I can add another calculation that will calculate the Year over Year Growth in percentage:
[Sum of RevenueAmount YoY%] =
DIVIDE(
([Sum of RevenueAmount]
– [RevenueAmount PreviousYear])
,[RevenueAmount PreviousYear]
)
In this calculation we divide the Sum of RevenueAmount minus the RevenueAmound previousyear by the RevenueAmount previousyear that results in the percentage growth. Adding this calculated field to the PivotTable gives us the percentage of growth for revenueamount:
I hope these blog post gave you a good overview of the capabilities of Power Pivot. Having said that this blog post will be just the beginning of your journey to getting to know Power Pivot and unlocking all its wonders.