skip to Main Content

finding magic part 2

The journey continues…

Five years on from my life-changing encounter with PowerPivot, my pilgrimage to the Nirvana of Modern Excel continues with some occasional detours.

When I first encountered PowerPivot back in 2013, I thought that this tool would obviously go viral and its use would quickly spread among mainstream Excel users so that the old-fashioned way of doing things would be replaced forever. I thought that it was inevitable that almost every Excel user would renounce vlookup or index-match in favor of the more robust alternative, PowerPivot relationships in the not so distant years to come. Unfortunately, I was sadly mistaken about this.

In my last post about my PowerPivot journey, I mentioned the various obstacles I had encountered at the beginning of my journey such as

  • Insufficient RAM
  • Suboptimal CPU
  • 32-bit vs. 64-bit issues
  • Version issues, etc…

These were all related to teething problems of my particular PC, which, with a varying degree of effort (which typically depends on individual corporate circumstances) could be fixed eventually.

So perhaps, the most formidable challenge I have encountered during my five-year journey has not occurred due to technological issues, but rather, has been related to human nature: resistance to change.

A small percentage of folks have staunchly clung on to the old-fashioned way of doing things and have resisted accepting the New Way. These individuals were comfortable and happy in the two-dimensional world of traditional Excel, and they did not want to have their working lives disrupted by the introduction of new technology which required them to, well…, learn new things. Contrary to general expectations, staunch traditionalists did not mind going back and forth between repetitive worksheets with hundreds of vlookup formulas, external file links, and engaging in hour upon hour of boring copy and paste work, and therefore, they did not have the motivation to learn the New Way to free themselves from these activities.

My initial reaction was to try and convert these traditionalists into Modern Excel users and to make them use Excel in the way which unlocks its true capability rather than just using it like sheets of paper. However, after a prolonged period of effort, I realized that due to individual mindset differences towards change and repetitive activities, a small percentage of Excel traditionalists will remain content to keep using Excel in the way that they have always done.

In addition, PowerPivot’s lack of universal use is compounded by the fact that it is hidden in a rather inconspicuous place in Excel.

The above are my main observations as to why the “New Way” has not spread as quickly as I imagined it would back in 2013.

Nevertheless, I must admit that I get a fair share of “wow’s” and “why has this been kept secret for so long?” reactions from other Excel users when I share what PowerPivot can produce. When I have these enthusiastic responses, I would happily send the links to my favorite PowerPivot introductory articles:

😊 A New Era

What is Power Pivot?

and tell the potential converts to enroll on the P3 Online University course to master the art of PowerPivot, and that the course was the most entertaining learning experience I’ve ever had in my life.

To a new user, typically I explain that PowerPivot DAX is similar to Excel formulas, but that the fundamental difference is that while an Excel formula is applied to a cell, DAX is applied to a column instead. And they get the point.

OK, that’s enough about the detour of my PowerPivot journey. In the rest of this article, I will talk about a great new feature I recently became aware of in Power BI, thanks to my interaction with the P3 team.

Interactive Power BI report on the web

Although I do not have much exposure to Power BI (yet), I recently learned from the P3 Web Technical Editor, that Power BI now has the capability to publish interactive reports on the web. I was very impressed. Hail to the great innovators who have created yet another amazing feature!

I’ve migrated the Excel model which I created for my last post to Power BI and put together an interactive headcount dashboard. The following are the techniques I used.

You can download the .pbix to follow along by clicking here.

Dynamic headcount analysis

While headcount analysis seems a conceptually straightforward topic, to do it flexibly, for example, by preparing a dynamic list of employees at any given point in time, contains some technically challenging aspects.

However, with the analytical capabilities afforded by DAX, a robust headcount report spanning across multiple years that can flexibly provide a snapshot of the number of employees and their names, tenure, and age at any historical point in time can be prepared with ease, from a basic employee database containing information on hiring and leaving dates.

I am using the employee table as a fact table rather than a dimension table so that multiple dates of joining, employee affiliations to different departments and different roles within the organization at different points in time can all be displayed. It is a fact that employees can transfer between departments, change their roles over the course of years as well as leave the organization and come back again at a later date at some point in the future.

To create an interactive headcount dashboard, you only need two tables,

  • An employee fact table
  • A calendar table

To enable slicing of headcount by time-dimension at any point in time, we have to keep the calendar table and employee fact table as disconnected tables.

Disconnected Tables

The DAX formula below provides a flexible headcount number at any historical point in time:

Headcount =
SUMX (
EmployeeFact,
    IF (
EmployeeFact[Start] <= [SelectedDate] && EmployeeFact[End] >= [SelectedDate],
        1,
        BLANK ()
    )
)

The above formula only sums up the number of employees from the employee database who were at the company at a given point in time (i.e., joined on or earlier than the given point in time), who at the same time, have not left the company, (i.e., left the company on or after that given point in time).

Where:

SelectedDate =
MAX ( ‘Calendar'[Date] )

Incorporating year and month to the row and column labels respectively in the matrix format creates a table of historical month-end headcount which can be cross-filtered by gender, role, location, and department.

I’ve also added a measure of the list of employee names using the ConcatenateX function:

Employee list =
CONCATENATEX (
EmployeeFact,
    IF (
EmployeeFact[Start] <= [SelectedDate] && EmployeeFact[End] >= [SelectedDate],
EmployeeFact[Employee]     )
)

But then, I decided instead that I’d like to add the age in brackets next to the employee’s name, and it turns out that ConcatenateX can handle such spontaneous tweaking with ease.

Employee list =
CONCATENATEX (
EmployeeFact,
    IF (
EmployeeFact[Start] <= [SelectedDate] && EmployeeFact[End] >= [SelectedDate],
EmployeeFact[Employee] & ” (“
& ROUNDDOWN ( [Employee average age], 0 )
& “) “
    )
)

Where

Employee average age =
AVERAGEX (
EmployeeFact,
    IF (
EmployeeFact[Start] <= [SelectedDate] && EmployeeFact[End] >= [SelectedDate],
        YEARFRAC ( [Birthday], [SelectedDate] ),
        BLANK ()
    )
)

The DAX formula on employee average age just calculates the age of the employees as at the selected date as the difference between that selected date and the birthday, where the employee has joined on or before the selected date and left on or after the selected date, (i.e., if they are employed by the company at the selected date.) Although the measure name says [Employee average age], when only one employee is involved as in the case of the employee name list, it just shows the age of that employee at the selected time point.

Including the department in the row labels, and role as a column label in another matrix table, we can obtain a list of employees with their age in brackets by department and by role at any given historical point in time which can be cross-filtered with the month-end headcount matrix table above.

Similar to the employee average age measure, I’ve also added an employee tenure measure:

Employee tenure =
AVERAGEX (
EmployeeFact,
    IF (
EmployeeFact[Start] <= [SelectedDate] && EmployeeFact[End] >= [SelectedDate],
        YEARFRAC ( [Start], [SelectedDate] ),
        BLANK ()
    )
)

 

I then added joiners and leavers measures.

Joiners =
SUMX (
EmployeeFact,
    IF (
EmployeeFact[Start] <= [SelectedDate] && EmployeeFact[Start] > [SelectedDate (Start)],
        1,
        0
    )
)

Where

SelectedDate (Start) =
MIN ( ‘Calendar'[Date] )DAY ( MIN ( ‘Calendar'[Date] ) )

Similarly for leavers,

Leavers =
SUMX (
EmployeeFact,
    IF (
EmployeeFact[End] <= [SelectedDate] && EmployeeFact[End] > [SelectedDate (Start)],
        1,
        0
    )
)

 

But then, I decided that I’d like to see the numbers and names of joiners and leavers on the dashboard in a similar manner as the headcount snapshot. To show this on the one-page dashboard, I used another disconnected table called “Slicer,” and enabled selecting measures using a switch function (a “Harvester” measure which is covered in the P3 training and previous blog posts).

Selected Headcount Measure =
SWITCH ( [Selected Slicer], 1, [Headcount], 2, [Joiners], 3, [Leavers] )

Where

Selected Slicer =
MIN ( Slicer[#] )

To reflect this change, I tweaked the [Employee average age] measure and the [Employee tenure] measure and replaced the formula calculating the headcount as at the selected date to [Selected Headcount Measure].

Employee average age =
AVERAGEX (
EmployeeFact,
    IF (
[Selected Headcount Measure] <> BLANK (),
        YEARFRAC ( [Birthday], [SelectedDate] ),
        BLANK ()
    )
)

I’ve created measures for the joiners and leavers’ name lists with the age in brackets.

Joiner list =
CONCATENATEX (
EmployeeFact,
    IF (
EmployeeFact[Start] <= [SelectedDate] && EmployeeFact[Start] > [SelectedDate (Start)],
EmployeeFact[Employee] & ” (“
& ROUNDDOWN ( [Employee average age], 0 )
& “) “
    )
)

Leaver list =
CONCATENATEX (
EmployeeFact,
    IF (
EmployeeFact[End] <= [SelectedDate] && EmployeeFact[End] > [SelectedDate (Start)],
EmployeeFact[Employee] & ” (“
& ROUNDDOWN ( [Employee average age], 0 )
& “) “
    )
)

I then combined them in another “Harvester” measure:

Selected Employee List =
SWITCH (
[Selected Slicer],
    1, [Employee list],
    2, [Joiner list],
    3, [Leaver list] )

I’ve split the average age into age band groupings.

Under 20 =
CALCULATE (
[Selected Headcount Measure],
    FILTER ( EmployeeFact, [Employee average age] < 20 )
)

20 to 29 =
CALCULATE (
[Selected Headcount Measure],
    FILTER (
EmployeeFact,
20<= [Employee average age] && [Employee average age] < 30
    )
)

30 to 39 =
CALCULATE (
[Selected Headcount Measure],
    FILTER (
EmployeeFact,
        30 <= [Employee average age] && [Employee average age] < 40
    )
)

40 to 49 =
CALCULATE (
[Selected Headcount Measure],
    FILTER (
EmployeeFact,
        40 <= [Employee average age] && [Employee average age] < 50
    )
)

…and so on.

Then put each of these measures on the values section of the funnel chart.

I did a similar form of banding for tenures:

(Please note that in order to economize on the real estate space on the dashboard, I’ve used short-hand expressions in the tenure measures and just stated “2 – 5 years” instead of properly stating “2 to less than 5 years” and so on.)

< 1 year =
CALCULATE (
[Selected Headcount Measure],
    FILTER ( EmployeeFact, [Employee tenure] < 1 )
)

1 – 2 years =
CALCULATE (
[Selected Headcount Measure],
    FILTER ( EmployeeFact, [Employee tenure] < 2 && [Employee tenure] >= 1 )
)

2 – 5 years =
CALCULATE (
[Selected Headcount Measure],
    FILTER ( EmployeeFact, [Employee tenure] < 5 && [Employee tenure] >= 2 )
)

5 – 10 years =
CALCULATE (
[Selected Headcount Measure],
    FILTER ( EmployeeFact, [Employee tenure] < 10 && [Employee tenure] >= 5 )
)

> 10 years =
CALCULATE (
[Selected Headcount Measure],
    FILTER ( EmployeeFact, [Employee tenure] >= 10 )
)

I then included each of these measures on the values section of the clustered column chart.

It’s worth noting that while all the measures previously discussed such as the headcount, list of names (with age in brackets), average age and tenure profile are all snapshots as of the selected date, the joiners and leavers measures sum the total number of joiners and leavers during the period of the two selected time points. If only one period is selected, that months’ joiners and leavers are calculated (from the beginning of the month to the end of the month.)

The report can be sliced, diced and chopped by multiple criteria, such as gender, role, different time points, department, location, etc., to name just a few dimensions. I’d also like to highlight the fact that the information contained in this compact, interactive one-page Power BI headcount dashboard is equivalent to a PDF document of well over 10,000 pages.  When all of the possible combinations of slicing and dicing are applied to the visualizations (without the clumsiness of having to scroll up and down the pages in what would be an incredibly bulky document to get the necessary information) it demonstrates the sheer fluidity, agility, and smartness of the data visualization which Power BI provides.

The above are just a few examples of many things that you can do with PowerPivot using the employee database which was previously only possible by using off-the-shelf specialized software. Thanks to the ability of PowerPivot to enable the employee database to be analyzed with a time dimension continuum using the disconnected table, we can flexibly turn the clock backward (and forwards too!) and get an analytical snapshot of the headcount demographics, and information about joiners and leavers for any selected period.

Enjoy slicing & dicing the interactive report!

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.

Sakiko Stickley

Sakiko is a Fellow of the Institute of Chartered Accountants in England and Wales (ICAEW). 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 8 Comments
  1. For many of us who are older than average (way older), age is very sensitive for us. We don’t feel safe when reports contain our age or even age brackets. Just saying….

  2. Hi! Thanks for the post. Very helpful, but I have a question. I’ve downloaded the .pbix file. Now how can I get the Power BI map to work with data from different locations? Your geospace dimension table in the .pbix file only contains a few cities.

    1. Hi Mark,
      Thanks for your comment. In order to customize the map for your own specific data with different locations, you can use the world cities database and add more cities with their latitude and longitude data to the geospace dimension table.

Leave a Comment or Question