As a Power Platform Consultant, this topic is extremely close to my heart. Analytics is so important to my clients, but do we all think the same when we say Analytics? The simple answer is “No”. For Enterprise Analytics, this is often forgotten. A single set of data can only produce a single set of insights and intelligence, right? Of course not. While the results of analysis of a specific entity must remain consistent, the presentation of those insights can be radically different. That presentation will also drive very different behaviours, and those behaviours could be positive when presented in one location or negative when presented elsewhere.
Let us assume that we have sales data, and our business goal is to increase sales by 1% month over month. The net result of this is that we want our business to have a 1% increase in sales each month. So, we take the Total Sales amount for a month and add 1% to it for the target next month. In our table below, for example, January sales of 1523 are used as our starting point, so February’s target will be 1539.
Using this model, we can see that some months, we made the target, and others we did not. This would likely be a view used by our finance team at the end of the year to describe how our organisation had performed. A great (retrospective) view of the year. However, the reality is that the question that will be asked during the year is more likely to be related to “Are we on track to meet our targets?”.
This all means that our first issue starts to appear, as we need to be able to understand how we answer that question in the middle of the current month. Getting this “wrong” or presenting this badly WILL cause your organisation significant issues. The chart below shows how this data can often be presented in months, using a simple split of target/days. A simple linear model is what most organisations use. It is easy to break the monthly target down to a daily number and use that. Some companies exclude weekends, so the line has flat spots for Saturdays and Sundays. For this article, we will look at a simple line.
We can see from the chart that we are broadly heading in the right direction. Our daily numbers do drift above target, and yesterday dipped below, so should we be doing something?
Now let us consider the reality. For many businesses, every day is not the same – there will be busy days and quiet days. So, should the targets be adjusted to reflect that?
Now let us consider the reality. For many businesses, every day is not the same – there will be busy days and quiet days. So, should the targets be adjusted to reflect that?
Looking at the same Sales to Target with a rudimentary daily track, we can see that our performance looks like it has not been as good as before, but that we are now ahead of target.
Variable targets appeal to many businesses, as they are a “better” reflection of reality, but what do targets drive? Targets establish a line mentality. Even the language of targets is linear.
The point of Analytics is to provide a dispassionate, numerical view of stories we use to manage our businesses. So, our actuals are always a reflection of what HAS happened in our organisation, while our targets are what we aim to be able to achieve in our business based on a hypothesis (to stay scientific). This is where the semantics of targets and how they drive the behaviours of people come in. The Target establishes a “finish line” that your teams are expected to cross. That behaviour is also reinforced by team leaders who will be pushing for their team to get to the target. Go back and read our example, with one model we are above target, and with the other we are below. How natural was that for you to consider?
Consider you are launching a new product that is expected to lead to an increase of X% in sales. We will track the sales amount over time and see if our plans are correct. So, we would prepare our sales teams for the launch, and we may take on additional warehouse and manufacturing teams to drive the highest volume of sales we can achieve. However, the aim is always to exceed the target by as much as possible. Psychologically, what happens when our business achieves its target? It is rare for the “push” to continue after completion of the target.
So, we need to consider how we handle our targets as we work through the month. Do we stick with the Linear Target progression, or should we use a variable target progression? The main push for variable targets is seasonality or holiday periods. It is always challenging to manage targets over extended holiday periods.
Let us consider the Easter period in the UK, when businesses are closed on Good Friday and often the Monday. The weekend can be quiet, meaning that often a monthly target is suitably reduced to account for that. However, that has a psychological impact on teams as they either get “ahead” of the target line early in the month or find themselves behind the line, “unable” to catch up.
This puts pressure on the analytics team to exclude “exceptional” days from target calculations. The hard truth is that daily, monthly and annual targets do not necessarily need to be cumulative. We may expect our daily targets to be 105% of what would be required to meet the monthly targets, which may in turn be 105% of the value required to hit the Annual Target. In truth, we must understand that targets, while important, are also diversionary. This can encourage bad or destructive behaviours across an organisation. Another bad behaviour that we are displaying (and that will also be carried out laterally across your organisation) is the subdivision of targets. The Monthly or Total Target is the minimum objective, and any attempt to split it either across regions, geographies or teams is not in the spirit of the target. All too often and I have seen it done. Team or geographic splits trigger a wider challenge as they can fuel rivalry rather than unity.
The lesson here is that your Analytics solutions must be applied and used with an awareness, not just of the success or failure aspects, but also with a firm knowledge that what is done on day one will never be perfect (just to add another spanner). Targets must drive the scope they are intended for and should avoid being broken down to “simplify” the progression towards their completion. The goal of your analytics platform must be to deliver an environment that allows ALL to access a view of organisational performance. The mindset of ALL is what encourages a combined data culture, where increased understanding and awareness of what goes on across the organisation leads to better strategic planning and better employee engagement.
This team and staff engagement in strategic planning and improvement is what will drive the continuous improvement of report and dashboard content. It will always lead to the expansion of the analytics platform as more dimensions and facts are added in a structured and managed manner. This, in turn, increases the business value delivered by the platform. That initial investment and first report content quickly leads to additional ROI, often exceeding initial projections – it would not be sensible or possible to quantify the third or fourth round of changes that will be undertaken to the model before starting.
The Data Literacy of your organisation will also increase as more people get actively involved in the reporting and planning landscape of your organisation – another reason not to let the subdivision of targets fracture the unity of your organisation. Take time to build proper targets at the appropriate levels. Remember, whatever we say about targets, these also apply to KPIs. KPIs should be something that supports the ongoing and constant performance of the business. When they are structured around targets, they will drive the same negative behaviours as Targets.
To calculate a month-to-date linear model, there are multiple options. To build the line based on just having a month-end target is relatively simple, however, it does require the generation of a daily target value. The result is some DAX that looks worse than it actually is.
This will produce a straight target line that you can then plot your sales against using the standard TotalMTD ( ), or a CALCULATE ( ) with a DATESMTD( ) clause. The calculate route may be preferable. You should place an additional filter on the day offset column, as you likely do not have future sales, and a horizontal line from the end of the sales data is rarely a value add.
To build a variable target line, you will have to build a DAX table. The reality is that the calculations for it dynamically will very quickly start to slow down your Power BI Model.
This will resultantly build us the above table. We are only interested in the DoW and PCT columns, enabling us to consider how we want to calculate the target line. Be aware that in this example, we have not filtered the dates. This would be sensible, so your percentage calculations are based on a more “current” set of data only, rather than ALL your sales. A 12-week filter may be sensible, but the choice is yours.
There is no firm rule to use to calculate the daily amounts, but the way I have calculated it for this example was to determine the PCT value for any given day and then divide that by the sum of PCT within the month. This returns a percentage based on the days in the month that can then be used to derive a sales amount by multiplying it by the target. Your specific measurements and how you calculate your percentages are the key thing. You may find it sensible to include these percentage values in a calendar (depending on how dynamic you want it to be). Moving calculations towards the source saves calculation time, but you can experience challenges in terms of data access.
Geordie Consulting has the experience and expertise your organisation needs to help you implement your target models. We have delivered KPI, Target and Budget reporting across multiple organisations and won’t just make this dynamic, but help you bring the meaningful insights that result from your work front and centre. This is the first step in making your organisation more data-driven. The next step is to implement a Data Centre of Excellence. Again, Geordie Consulting has the experience you need. We’ll work with your existing teams, identify the opportunities and challenges they face and help you build appropriate training plans to upskill and enable your teams. They can then apply their subject and business knowledge to building and delivering business-transforming data models and reports that provide next-level analytics of the specific challenges and opportunities you face.
Have you started to use Power BI, but it is not making headway in your organisation? Have you got some reports together but lost momentum? Do you not know what the next step should be in your corporate journey with the Power Platform? Have you lost faith in the Power Platform and see it as a waste of time? Then book an introductory meeting now and start your real data journey. Scan the QR code or click the link on our LinkedIn page. We can answer your questions, and together, we can plan for your future.
