Project Brief

The summary brief of this project was to build a top-level KPI dashboard for the executive team. There needed to be an ability to add future data and update the report.

Philosophy and Approach

Distilling the brief, my approach here was to develop a report that was:

• single page
• global
• high level reporting (details can be provided in other reports)
• reflecting the latest reporting periods rather than complete history
• setting performance targets and self measuring
• covered areas of sales, shipping, products and customers

Single Page

Executive level KPI reports need to capture a high level snapshot, and provide a balance of succinctness and sufficient detail to know overall performance and point the way to any areas they may wish to further explore elsewhere.

Global

There is the potential to look at individual regions and countries, but an executive team may wish to see overall global performance first. Again, region and country performance may be explored elsewhere in more detailed reporting.

Latest Reporting Periods

The executive team is likely interested in the current reporting period (month, quarter, year) and how the company is trending from previous periods. Providing details on what was happening 2-3 years ago is probably irrelevant unless there is some cyclic trend.

I omitted the "live data" from the month of May 2015. As the month was only a few days old, reporting against targets set for a month may be skewed. This might be more suited to a daily operational dashboard.

I focused on the Reporting Month (RM) and Reporting Year (RY). The RM was the previous complete month (April 2015) and the RY was the year to date for 2015 (Jan, Feb, Mar, Apr).

Setting Performance Targets

In the absence of direction, I set arbitrary 2015 targets for revenue, discounts and delivery that I could measure performance against. These were:

• Revenue: $100k per month

• Discounts: Minimise to less than 6.5% of gross revenue

• Delivery: Maintain an on-time* rate >95%

• I took any shipping date that was less than 3 days from the required date as being either "at risk" or "late", and therefore not "on-time."

Key Reporting Areas

Revenue

I looked at net revenue and discount rates for full reporting periods (RM, RY) and measured both against targets and previous periods.

Deliveries

Similarly, I measured on-time delivery rates against targets and previous periods. I went one step further to look at shipping company performance, also taking into account their average freight costs. I flagged higher freight costs in yellow as something to check. I wouldnt flag it as red, as higher costs may be associated with larger or bulkier items, or potentially additional customs paperwork.

Customers

I looked at the top customers for the period for total revenue, and then compared to the performance over the year. As an additional angle, I analysed the average order value for the month from that customer, and checked to see whether the value was above or below the yearly average.

Products

I performed a similar analysis for products.

Technical - Design

Modelling

I wrote a LinkedIn post about my approach to the data dictionary and creating the model relationships.

KPI Cards

I created the KPI cards using the standard line and bar charts in Power BI, but harnessed the new Title and Subtitle mods along with conditional formatting to create dynamic big KPI number and KPI performance metrics. This is a great way to build almost custom KPI cards.

Deneb Visuals

I have been learning deneb and vega-lite recently, and took this challenge as my opportunity to experiment and create some customised visuals, including bullet charts and combined overlapping bars and lollipop charts, all with dynamic color formatting.

Overall Design

Overall goal was to keep things relatively simple, highlight the targets, have 4 main KPI cards across the top, then have that extra level of detail below each card.