The Journey Begins
I am an accountant fascinated with data. My PowerPivot journey started in 2013 when I encountered the P3 Adaptive blog while googling to find a way to tackle a multi-dimensional financial reporting task. Because of the limitations of traditional Excel, with its two-dimensional world of rows and columns, this task would have taken a significant amount of time and only produced a sub-optimal output. A six-figure USD price tag and some other reasons made seeking a traditional BI solution not feasible.
While searching for a better alternative, I encountered Rob’s article and found out about a free Excel tool (the then add-in) called “PowerPivot.” If there was one moment in my life where I felt like I had been struck by lightning, finding out about PowerPivot while reading the P3 Adaptive blog post was that moment. It made me realize that there was a limitless multi-dimensional world beyond the flat confines of traditional Excel. I was instantly excited about the possibility of what it could do, and since that time I have never gone back to the old-fashioned way of doing things.
In the early phase of my PowerPivot journey, I purchased a couple of PowerPivot books (Mr. Excel, Rob, etc.,); but then the real game changer for me was enrolling on the P3 Adaptive online training course which I enjoyed immensely and would recommend to anyone who uses Excel regularly in their daily work. The course was literally the Hogwarts of data wizardry for me.
I soon noticed that PowerPivot relationships are so much more robust and reliable than the traditional method (i.e., vlookup or index match) alternatives because of the built-in rejection mechanism for many-to-many relationships. I discovered that the results yielded by using the traditional method are sometimes fundamentally flawed and unreliable. When I tried to convert them into PowerPivot relationships, I received many-to-many errors. However, instead of being put off by the many-to-many errors, those errors only heightened my estimation of this tool, and I would now always choose PowerPivot relationships over those traditional alternatives.
I started experimenting with PowerPivot at work and was always amazed at what it could do. But, my PowerPivot journey was not always smooth and easy. I encountered numerous obstacles:
- Version issues (for the old Excel 2010, PowerPivot was only available in a Professional Plus version)
- 64 vs. 32-bit issues
- Insufficient RAM issues
- Sub-optimal CPU, and many others
Nevertheless, I eventually managed to overcome these issues and with them gone, I became the happiest and most productive data wizard ever in my career.
When I was a novice PowerPivot user back in 2013, when I could not solve a problem by myself after a few days of googling and pondering the issue, I would contact the BI world celebrity Mr. Chris Webb for a private consultation. To my amazement, he responded to my questions quickly and also shared with me many valuable techniques.
The Payoff
Within a month of encountering Rob’s article, I had managed to set up a basic interactive multi-dimensional P&L reporting system that a BI software vendor had told me would cost a 6 figure (USD) sum to complete, and I did it without incurring any cost to the company. PowerPivot empowered me, an ordinary Excel user, who had just started to learn DAX, to accomplish in one month what was previously only deemed possible with the purchase of an expensive and lengthy BI project.
I reported the output of my PowerPivot experimentation to my CEO. Now he could get a clear view of his company’s P&L by business units and product lines comparable year on year and month by month. The traditional Excel route would have resulted in a cumbersome, rigid and awkward workbook of over one hundred worksheets which he would have had to keep switching back and forth between in order to get a detailed view of what he wanted to see. On the other hand, with PowerPivot, the information equivalent to hundreds of worksheets was condensed to a smart, single-worksheet, multi-dimensional, morphing report with an interactive slicer user interface. The report could be drilled down to provide instant traceability and accountability to accounting transaction entries making up the reported figures. What’s more, this robust watertight traceability was even true for the allocated costs, which were recorded in common cost centers not associated with specific business units and product lines in the bookkeeping transaction database.
Before encountering PowerPivot, I considered that the only proper way to get that transaction level drill-down of allocated costs assigned to a specific product line was to physically assign product codes during bookkeeping, which was simply out of the question, because of the sheer labor-intensity it would require. However, PowerPivot effortlessly allowed a virtual transaction table to be created even for those distributed costs, split into business units and product lines with absolute clarity and sharpness.
My CEO described it as “Magic” and gave me the thumbs up to carry on with the journey I had started. In fact, he was so impressed with what PowerPivot had produced that he recommended the people in other departments to also start using this tool!
In the following months, as my DAX skills continued to grow, I was able to make many improvements and refinements to this initial model.
The Technique
Visualization of employee length of service
With the power of DAX, I explored and visualized many different datasets in a way I had not been able to previously. In one instance this related to the visualization of employee tenure. Typically, in a smaller organization, the use of the employee’s timeline chart is as shown below. It allows the full visualization of all of the workforce changes that have occurred (joining, transfer between departments, change in roles, leaving, and even returning).
I created a dynamic, slicerable, timeline chart that shows employee tenure status at any given point in time. The chart can be sliced by multiple factors such as gender, role within the organization, department, etc. In this particular example, a square represents a staff role, and a triple line represents a managerial role while the red color is used to denote female gender and blue male. Filtering by slicer provides additional insights into workforce changes over the course of the visualized period.
To prepare this particular timeline chart, you need only two tables.
- An employee fact table which includes data such as the start and end date reflecting all workforce-related changes including the date of joining, leaving, date of transfer to another department, change in roles within the organization, etc…
- A calendar table
The calendar table and employee fact table remain as disconnected tables.
The only data fields which remain non-changeable over the course of time are such fields as the employee code, gender, and date of birth. All of the other employee information, such as the date of joining, date of leaving, department, roles, etc., can change over time. For example, an employee can have multiple dates of joining and leaving, can belong to various departments at different time points, and can have numerous roles over the course of many years. Although it is standard practice to use the employee database as a dimension table, by using it as a fact table, we can flexibly visualize the changes in roles and departments. An example of this is demonstrated in the dynamic image below which shows the leaving and returning and the gap period for “Blair Kerr” who had once left the organization and then came back at a later date.
To visualize employee data in this manner, we need to use a formula like the one below and show a symbol in the period of employment:
=
CONCATENATEX (
EmployeeFact,
IF (
EmployeeFact[Start] <= [SelectedDate]
&& EmployeeFact[End] >= [SelectedDate],
EmployeeFact[Grade symbol],
BLANK ()
)
)
where SelectedDate :=
MAX ( ‘Calendar'[Date] )
and
where EmployeeFact[Grade symbol] is a calculated column as shown below.
I’ve used Calibri font to indicate ▬ and g for staff and manager roles, respectively, and reduced the column width to achieve the timeline visual effect, and converted the pivot table to cube formulas (Analyze →OLAP tools→Convert to formulas).
The timeline visualizes employee tenure and the timing of the change in roles within the organization, and the report can be sliced and diced to provide instant visualization concerning specific selected slicer criteria.
This is just one of many things which you can do using PowerPivot that was simply not possible in the two-dimensional world of traditional Excel. Your imagination is the only limit to what you can do with PowerPivot.
Microsoft’s platform is the world’s most fluid & powerful data toolset. Get the most out of it.
No one knows the Power BI ecosystem better than the folks who started the whole thing – us.
Let us guide your organization through the change required to become a data-oriented culture while squeezing every last drop* of value out of your Microsoft platform investment.
* – we reserve the right to substitute Olympic-sized swimming pools of value in place of “drops” at our discretion.
