How to build a challenge dashboard: A top-level KPI dashboard for executives (Challenge finalist)

Tools used in this project
How to build a challenge dashboard: A top-level KPI dashboard for executives (Challenge finalist)

Northwind challenge dashboard

About this project

Please click on the "open in new window" button, total bottom right, and use the little blue arrows bottom left and right to navigate through the dashboard


This dashboard is built for several purposes

  • as a top-level KPI dashboard for the Northwind executives
  • to find insights & recommendations to share with the VPs
  • to practice new features
  • to prepare a PowerPoint session about the DoD, the Definition of Done
  • to build a dashboard in our Inergy house style to use for Linkedin posts
  • to finally win the Maven challenge badge

Both because it is an executive dashboard, so very high over, and because of the lack of historical data, I've chosen to build a Monthly scorecard with only the last completed month visible (April 2015) with a monthly tooltip for each KPI.

Plus 4 extra analysis pages and a hidden details page visible by drill-through.

In the real world, with real day-to-day data, this scorecard will be updated monthly, or daily if wanted.

The Data

There are 7 CSV tables to start with.

To complete the dashboard which I would like to have, I added a calendar table, a continent and country table, and a managers table

undefinedFor analyze reasons, I added some columns to the Orders table.

  • ordered too late, if ( required date - order date ) < 2
  • not shipped yet
  • shipped too late if ( required date - shipped date ) < 2
  • desired delivery date, 2 , 4 or 6 weeks

As the delivery date isn't given, I assumed that transport takes 2 days, this 2 days is easily changed if the assumption is incorrect.


The Analysis

The data shows a company that is growing fast. The given data starts in July 2013 and ends in May 2015. Because the dashboard is meant to be used by executives, I've chosen to show only the scorecard of the last completed month, April 2015.

I defined 5 KPIs that will help to control the company. Per KPI the value of the last month (April in this case), the value per month of the current year (2015), YTD, and the top 3 companies and products, both YTD, are shown.

With a tooltip, the top 5 per month will be visible as well.


For insights and recommendation purposes, I added 4 analysis pages together with a details page.

The Visuals

For the executive's scorecard, the visuals are pretty simple. 5 KPI's are plotted on 5 white shapes. Plus a tooltip report page per KPI.

Only Power BI standard visuals are used.

  • 2 KPI visuals
  • a line chart
  • 2 tables
  • a multi-row card for the tooltip
  • 2 bar charts for the tooltip

For the tables, I used white column headers

In the embedded version above, the Azure map is not supported. This is how that report page looks like with the map. You can zoom in and out and click on every single country to see all details on the page change for that specific country.


The Key Insights

The key-insights for Northwind executives are easily found on the scorecard page (see above). Green is better than last year, red is worse. The line charts give you a visual trend line, this year vs last year. When you hover over the line charts, more details per month will appear.

On the right the top customers and products are shown, YTD.

Main Key-insights top level are;

  • all KPI's, except "Shipped too late" are higher than last year's same period.
  • for net sales and number of orders, that's good, for % discount and freight, it better be lower.
  • thüringer Rostbratwurst is by far the most ordered product and the top 2 net sales. But it also is the top discounted product
  • save-a-lot-market is a company that is high at the top of all KPIs, positive and negative. Especially the % discount is much higher than the number 2 (15,1 vs 10,1 %)

A couple of insights I found in the extra analysis pages are;

  • the total average discount increased by 60,8%, from 3,1 % YTD last year to 5,0% YTD this year.
  • the highest given average discount is in the category Meat and Poultry, with 12,4%, while the average of 2,7% for Grains and Cereals happens to be the lowest.
  • United package has the most orders shipped too late, 5,6%, slightly better than last year.
  • Margaret Peacock had must orders YTD42, that's 61,5 % more than last year. The Nett sales were lower than last year, 49 k$ vs 57 k$ last year. 9,5 % of her orders were shipped too late.
  • most customers order 4 weeks in advance

The Design

The design of the scorecard and report pages is based on the standard layout I'm used to working with in my daily work at Inergy. I like the clean design and alignment.

I've found the Northwind logo on the internet and I used 1 of the blue colors for the dashboard design.

Top left there is a KPI table with a measure field parameter. If you change KPI, the full table is changing.



In the end, I put some extra time into cleaning up the whole dashboard and making it more user-friendly. Things I did;

  • grouped the measures in folders
  • deleted unnecessary header icons
  • cleaned up the filter pane, only needed filters are visible
  • added dynamic titles to all visuals
  • edit visual interactions
  • checked the KPI card colors, distance to goal
  • added navigation arrows at the bottom
  • added invisible buttons, with a tooltip, over the Northwind logo to navigate back to the scorecard
Discussion and feedback(2 comments)
Romero Neijhorst
Romero Neijhorst
about 1 year ago
Once again you get the job done! Talk to you asap
2000 characters remaining