Skip to main content

Dashboards, PowerPivot, Slicers, and DAX

Things you can do with Excel 2010

Why PowerPivot?
Dashboard Introduction
PowerPivot Example
PowerPivot Formulas
PowerPivot Resources
Dashboard Introduction
My Boss Just Came Back From Another Management  Conference
And Now He Wants Me To Make A Management Dashboard
Here’s the situation: your boss just came back from a seminar. Now usually that means trouble – more work for you. And this time the boss has it in his head that he needs a “Management Dashboard”.
As usual you are the person he turns to with a new idea.
With fear and trepidation you do a web search. You google “Dashboards”.  Now anytime google  comes back with a bevy of paid ads at the top of the page that means that there are a bunch of people who are trying to sell something related  to Dashboards and they are not above trying to make the subject seem more complicated than it is in an effort to sell you their complex (and often buggy) software or convince you to buy their 700 page book or take their expensive online class.
You discover a  definition of Dashboard on a website called “Dashboard Spy” – which you figure must mean they spy around capturing examples of other people’s dashboards.
Dashboard Spy’s definition goes like this:
Known by many names…. (a dashboard) is basically a way for business users to get an at-a-glance understanding of metrics of importance to them. In addition to acting as a summarization device, the dashboard also serves to highlight specific data and allows the user to drill down and inspect specific items. It allows a browsing style of user interaction in addition to the usual menu based navigation.
In reading a few more articles about dashboards you repeatedly run across the name of a data visualization guru named Stephen Few. His definition of a dashboard goes like this: 
A dashboard is a visual display of the most important information needed to achieve one or more objectives; consolidated and arranged on a single screen so the information can be monitored at a glance.
Just as the automobile's dashboard provides all the critical information needed to operate the vehicle at a glance, a…dashboard serves a similar purpose whether you're using it to make strategic decisions for a huge corporation, run the daily operations of a team, or perform tasks that involve no one but yourself. The means is a single-screen display; the purpose is to efficiently keep in touch with the information needed to do something.
Next  you  search google images and find  some sample dashboard reports:
This dashboard from the Indianapolis Museum of Art seems to meet our criiteria:
1) Easy to read
2) Combines data from different systems
3) Provides an at-a-glance understanding of key measures
4) Can be interactive – click for more information
Now let’s just pretend that since you are are familiar with Excel you had heard  that there is a new feature called “Slicers” in Excel  put there especially to help people build dashboards. You do a google image search on “Slicers Excel” and find this dashboard:
Is it
1) Easy to read ? Yes
2) Combines data from different systems? - Yes
3) Provides an at-a-glance understanding of key measures? –Yes
4) Can be interactive – click for more information? - Yes
The “Slicers” are the boxes along the left edge that contain buttons you can use to select Month, Day, Time of Day and Location.
Doing another google search you find a dashboard with a business focus
In this example the slicers are on the top and the right. By clicking any combination of Dates, Regions, or Products the user can see Profits from the financial system, combined with data from the Sales system.
You learn that “Dashboards” are often used to keep score.

This dashboard shows the score by comparing sales to budget targets.

Now in your web search you run across the initials “K””P” “I”. This stands for “key performance indicator”. There is quite a lot of information about how to choose to right KPIs. This makes sense to you –It is difficult to focus attention on the right measures.

By now you understand that one key advantage of a Dashboard is that it can focus the attention of people on what is important. It can do this much better than a bunch of separate reports. A dashboard invites analysis of the data.
You suspect that the biggest advantage in creating the dashboard is that the people involved have to agree on the key measures that are important enough to appear on the dashboard.
In fact you think it would be a great improvement if your boss had a dashboard to keep him on track. Too often it seems like there is a “goal of the month”. If the boss could measure the same things month after month then you and the rest of the team could make more progress. It seems like a dashboard could influence the boss to have a “more steady hand on the tiller.”
But some of the solutions you have run across require a large investment in software. Some of the solutions are so complex that outside consultants are required. It seems like the mechanics of a dashboards often distract from the important activity of choosing the right measures and focusing attention on achieving targets.
If something takes many months to develop you know the boss will lose interest. You wonder if a “Dashboard Building Process” will be just another passing fad.
The biggest challenge will be deciding what to measure. Some of the measures are obvious. But others will require buy-in by many people on the team ---- as well as the boss.
The second biggest challenge will be getting the data together in one report from many different systems. And it is not just a matter of getting the data together once a quarter.
You know that to keep the attention of the distractible people on the team the dashboard will need to be updated monthly – or maybe weekly. There is no way that you can spend hours each week of month creating an Excel report by hand.
So – the dashboard will be:
1) Easy to read
2) Combines data from different systems
3) Provides an at-a-glance understanding of key measures
4) Can be interactive – click for more information
5) Tracks progress on key measures
The project will be a success if the team (and the boss) can work together to come to consensus on which key measures and targets deserve everyone’s focus.
And you need a flexible efficient way to prepare monthly or weekly versions of the dashboard.
With your new understanding of Dashboards you prepare this plan:
Step 1:  Gather sample dashboards for your industry. Dashboard Spy and other web resources might help you here.
Step 2: Present the samples to the boss and get his input on a first set of measures for a prototype dashboard.
Step 3:  Meet with Sue in IT to find out how to get to the data needed for the prototype dashboard.
Step 4: Prepare prototype in Excel using the new slicer feature. Excel also has a data base capability built in called “Powerpivot” that  can make the task of getting data from IT and doing monthly or weekly updates more manageable.
Step 5: Get input from the team. Use the new prototype dashboard to get people’s attention. Add additional measures to the prototype. The flexibility of Excel along with slicers and PowerPivot database features might be sufficient to get through this step without a large expenditure of time and software costs. After all everyone has Excel on their laptops. You can distribute the first dashboards by e-mail.
Step 6: Evaluate every six months. Are the dashboards getting any attention?  Is it worth devoting more attention to the concept of dashboards?
So….You are ready to go with this plan…you have learned about dashboards and thought through a plan for building a prototype.
Now if the boss can just stay focused on the idea of building a management dashboard and not be distracted by the next shiny object that comes along.......