At some of my most recent customers I've developed an efficient way to analyse & deliver more meaningful, actionable and business-impactful insight to the users. I'd like to share this with you!
This is based on Power BI, so I'll talk about DAX and Power BI report design, but it could be adapted to any tool.
Using the Adventure Works sample database I created a couple of examples. It reads like a DevOps User Story, which is good as it switches to being busines-driven over data-driven.
Some of our customers have an SLA on shipment times. If we miss it, we are financially penalised.
We have strategic Customers and Products. I'd like to know if any of them have a consecutive drop in sales over X quarters or months
I'd like to know if any of the top X Products or top Y Customers have a >20% drop in sales month on month
There are many more questions like this. And they can change over time, so should be adaptable. The Business Metrics are not quite KPIs, so we're not developing a scorecard. These metrics are more tactical to action something impactful, right now. How to we solve for this?
Individual Business Metrics
Create a DAX measure for each of the questions. It is a specific calculated measure and can indeed be quite complex. That's OK because it's effectively filtering, slicing/dicing & comparing data to tell you that something needs to be done. See the below examples. Pardon the laziness on the DAX, as I've been quite verbose & hard-coded some date info.
// This Business Metric counts the number of Top 100 customers who had a >20% drop in sales in the most recent quarters
// Pardon laziness in hard-coding some things!
// This could be further improved by doing current Quarter-to-Date, comparted to previous Quarter-to-date
Customers 20% Sales Drop =
CALCULATE (
SUMX (
ADDCOLUMNS (
CALCULATETABLE ( VALUES('Customer'[Customer Code]), 'Customer'[Customer Sales Rank] <= 100),
"@DropCheck",
VAR vPrevQuarter = CALCULATE ( [Sales Amount], 'Calendar'[QuarterKey] = 35, REMOVEFILTERS('Calendar') )
VAR vLastQuarter = CALCULATE ( [Sales Amount], 'Calendar'[QuarterKey] = 36, REMOVEFILTERS('Calendar') )
RETURN SWITCH(TRUE(),ISBLANK(vPrevQuarter),0,ISBLANK(vLastQuarter),0,vLastQuarter < vPrevQuarter, 1, 0 )
),
[@DropCheck]
)
)// This Business Metric calculates the number of top 50 Products which had a fall in Sales for three consecutive months.
Products Consecutive Sales Drop =
CALCULATE (
SUMX (
ADDCOLUMNS (
CALCULATETABLE ( VALUES('Product'[ProductKey]), 'Product'[Sales Rank] <= 50 ),
"@FallingCheck",
VAR vMonth1 = CALCULATE ( [Sales Amount], 'Calendar'[Month] = DATE ( 2013, 10, 1 ) )
VAR vMonth2 = CALCULATE ( [Sales Amount], 'Calendar'[Month] = DATE ( 2013, 11, 1 ) )
VAR vMonth3 = CALCULATE ( [Sales Amount], 'Calendar'[Month] = DATE ( 2013, 12, 1 ) )
RETURN IF ( vMonth1 > vMonth2 && vMonth2 > vMonth3, 1, 0 )
),
[@FallingCheck]
)
)Business Master Metric
The business metrics should generally be designed that if any of them return a result >0, there should be a notification to the relevant people for business review & mitigation plans.
Therefore, I create a Business Master Metric, which checks every one of the Business Metrics and simply returns a 1 if ANY of them are past their threshold, otherwise a 0. This Master Metric can be used to pin to a Power BI Dashboard & subsequent alerts set up and/or Power Automate Flow to take any more specific action. One Master Metric to rule them all!
Metric Master Tier 1 =
SWITCH(
TRUE(),
[Customers Shipment SLA Penalty Risk]>0,1,
[Customers 20% Sales Drop]>0,1,
[Customers Consecutive Sales Drop]>0,1,
[Products 20% Sales Drop]>0,1,
[Products Consecutive Sales Drop]>0,1,
0)The Process to Monitor & Action
Here is the process to introduce:
Master Metric: alerts us that something needs attention, bring us to the dashboard
Metric Summary: A one-pager of every metric to show which one(s) have past their threshold
Metric Detail: A drill-through to a specific page, breaking down why the metric failed. i.e. which Customers are in trouble, which important Products are not selling, etc.
Take a look at the below Power BI report, which brings you through the experience. Here are the main points
The home page clearly segments Tier 1, Tier 2, Tier 3 metrics. Some adjusting would be needed as the quantity of these increased
Colouring used to quickly identify which Tiers are at risk
Each Business Metric card visual with colouring (the value & subtle shading) to indicate an issue
Each Card with a direct link to a drill-down on which customers/products affected
Also note the easy navigation incorporated to quickly bring the user to the right place, along with clear definitions of the metrics and a Support/Feedback page to round off a nice user experience.
I suggest hitting the full-screen arrow at the bottom-right to get the complete user-experience & sizing.
I'd love your thoughts & feedback on the navigation experience!
Criticality, Adding Tiers
I suggest breaking the metrics into different Tiers. Where anything classed as Tier 1 means it's critical and should be actioned immediately. You can have lower Tier metrics which don't invoke as much urgency, but still require attention.
Some of the examples I gave at the top of this blog could realistically be Tier 2, as a drop in sales of strategic products may not be deemed as mission critical.
Power BI Metrics
As an alternate option to the report/dashboard process I used, you can also leverage Power BI Metrics to monitor your Business Metrics.
I also created a summary page using Power BI Metrics and shared it as a screenshot in the above report. Follow the link at the bottom of the main page to see the screenshot.
Sharing the love
The example PBIX file is saved in GitHub gives a full breakdown of the metrics, with the DAX, nicely organised folders!
From a Customer point of view, this model has driven very high positive feedback. They don't need to spend time slicing & dicing & know that the most crucial types of (measurable) insight is brought to them.
Additional Reading, more background
I move the below to the bottom of this blog as an appendix, as it was simply too wordy. Feel free to read for additional context.
For anyone who has been developing Business Intelligence solutions for any amount of time, around 80% of the effort is the engineering covering data acquisition, transformation, modeling, deployment, security. However even a perfectly designed solution can leave a lot of insight unlocked and ultimately the return on the investment is reduced. The data engineering solves the most common issues like easy access, fast to market report creation, migration of some old manual process to automated dashboards - but the value to the business is really only the time & effort saved. People tend to build the same reports again. Ho hum :-(
Once the engineering has been done - you need to switch your attention to business leaders and group heads to ask what kind of analysis will help them change the bottom line. It's a consultative process with probing questions, because a lot of the time, they don't know or haven't had to articulate it before.
The answer is to develop Business Metrics which answer very specific questions that have a direct impact on making money or saving money. A data engineer (like me and you - if you are a developer of solutions) will have very little business context on the data you've been wrangling. Once you have engaged with business users and asked specific questions that they would like to know the answer to every day, then you can start building out a very useful reporting layer.
Comments