finding the magic banner

The Journey Begins

I am an accountant fascinated with data. My PowerPivot journey started in 2013 when I encountered the PowerPivotPro 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 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 PowerPivotPro 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.

  1. 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…
  2. A calendar table

The calendar table and employee fact table remain as disconnected tables.

Calendar and Employee table

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.

grade symbol

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.

Employee timeline

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.

  Subscribe to PowerPivotPro!
X

Subscribe

Sakiko Stickley

Sakiko is a Fellow of the Institute of Chartered Accountants in England and Wales (ICAEW). She started her career in a big 4 accounting firm in 1999, and after having worked in multiple accounting firms advising a number of Fortune 500 companies, moved on to work in industry where she could make a real business impact by turning raw data into actionable business insights. In 2013, she became one of the first PowerPivotPro converts in Japan, and since that time, she has been happily automating her daily reporting work using DAX and M. 

This Post Has 6 Comments

  1. Hi Sakiko, thanks for sharing your experience. What size of RAM did you find worked best for the largest model you were working on?? Thanks, Tom Pannett

  2. Hi Tom,

    Thank you for your comment. When it comes to RAM, the larger the better. The largest RAM I had on my PC was 64 GB, but generally speaking, 32 GB works just as well. I think the cost of RAM is a tiny investment when compared with huge benefit you can get from having a PC which can deal with millions of rows of data without crashing.

    Best regards,

    Sakiko

  3. Hi Sakiko,

    I enjoyed your post, thanks for sharing your experience. I’m on the Power Pivot journey too, perhaps a little bit earlier on than you.

    One thing I can’t work out, why was concatenate image needed?
    In my head, only the IF was needed to populate the indicator..
    What was concatenated?

    Thanks in advance for helping me understand.

    All the best,
    John

  4. Hi John,

    Thank you for your comment. There is a golden rule of DAX measures prohibiting the use of “naked columns” in if formulas. An old article in the link below discusses this point.
    https://powerpivotpro.com/2011/03/the-magic-of-ifvalues/
    However, we can break that golden rule and just put a column name in the second argument to X functions. In my example above, I used ConcatenateX to aggregate the text of ▬ and g.

    Best regards,

    Sakiko

  5. Hi. I’m working as an accountant in the finance department of a company.
    I wish I could use PowerPivot at work like you. I need to learn and practice more and more.
    I really enjoyed your story!

Leave a Comment or Question