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.
Distilling the brief, my approach here was to develop a report that was:
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.
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.
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).
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."
I looked at net revenue and discount rates for full reporting periods (RM, RY) and measured both against targets and previous periods.
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.
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.
I performed a similar analysis for products.
I wrote a LinkedIn post about my approach to the data dictionary and creating the model relationships.
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.
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 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.