Project Scope

The scope of this project was to develop an interactive dashboard that enables sales managers to track their team's quarterly performance outside of their CRM system.

Thought Process

I would say as soon as I saw the dataset, I had an outline plan and pencil sketch of what I wanted to build within 30 minutes, but that was the easy part!

I saw the data had a series of regions, and within the regions there were managers who had around 5-6 sales agents reporting to them.

So my high level concept was to have a series of sales metrics, and then compare them at a:

• global level
• regional level
• team/individual level

This gives the ability to "benchmark" the managers team and individuals against each level and get a wider appreciation of their performance.

This would form the matrix you see in my finished product.

Added to that, I wanted to give the ability for a manager to select themselves and any given quarter period and see the results for that quarter, as well as quarter over quarter (QoQ) performance.

I was going to leave this at that, but after a little more of a deeper dive into the data, I thought it would be beneficial for managers to also get an idea of product sales performance so they could potentially find their own trends either with the products or their agents.

As a manager, this type of information is what I would like to see.

Data and Modelling

There wasn't too much data cleaning required, and the model itself was quite straightforward. The main point was to ensure a connection between the Date and sales tables to connect both the engage and close dates. The engage date was taken as the active relationship as it was primarily used when calculating my metrics.

Metrics

I guess the main part was defining the metrics. The type of metrics I used was determined by the data available, so you have to "cut your cloth to fit".

Below are the five main metrics I looked at. Individually, they are interesting, but the real insight comes when you see them together, the context and counterbalance that brings, and it can allow a manager to get a clear picture of their teams performance.

This is primarily why I wanted to show all metrics at the same time on a single page, so the manager has that visibility and context when reviewing.

Wins

This is fairly self explanatory. There are sales leads that are either still in some sort of engagement, or have been won or lost. Here, I just wanted to track the wins. This is a very basic metric, but can give a basic idea of magnitude of performance, especially when looking at QoQ.

Win %

The win percentage is a slightly more detailed metric. Here, I divided the number of wins by the sum of wins and losses. This is basically a success rate on how your agents can close a deal.

Average Days to Close

This is another important metric, and measures the average number of days between engagement and closing a winning deal, essentially an "efficiency" metric. The shorter the timeframe required to close a deal, the more time the agent has to pursue other deals.

Average Sales

This looks at the average dollar value of a successful sale. Again, this is important in order to provide full context. A sales agent may have 50 wins and a 90% success rate, but only takes in a $50 average for each sale. Another agent might have 5 wins and a 50% success rate, but averages $2000 per sale.... who is the better performer? This metric can provide that counterbalance and context.

Average Variance from RRP

Finally, we had details for the sales price and also the recommended retail price (RRP). I wanted to measure what the variance of the actual sales price from the RRP to see if the agents are potentially underselling goods in order to make a deal. As a manager, I feel I would want to track this. Ideally there might be a target limit the manager sets for the team, and you could maybe set alerts against that if you would implement this in real life.

Structure - Top Down

Header

I wanted my header to be the location where a manager selected their team, quarter period and the type of report they wanted to see. In real life, you may want to apply some kind of RLS so that views can be limited to only a manager, or a region, but I didn't have the luxury of setting that up here

I also wanted to provide some kind of dynamic personalised greeting for the manager, and confirm the team details. That way if they needed to screen shot the report, etc, the details would be there. The dynamic parts of the greeting were done using SELECTEDVALUE() type measures.

Finally, I thought it important to define what the metrics mean on the report. This was done via smaller font as a note. Potentially, as the users got more familiar with the report, these could be removed, or moved to a notes page.

Card Visuals

The metrics were displayed in the new card visuals, and formed a consistent hierarchy of:

• All Regions
• Team Region
• Team

This provides that increased granularity as you move down the page, and, with added notes, also provides comparisons with each step of the hierarchy

To achieve these type of metrics, I employed the below DAX, using % of Wins as an example. We use "REMOVEFILTERS()" to do just that, find values that ignore any slicer/filters applied on the report.

% of Wins for All Regions:

_% Wins_All Regions = 
CALCULATE(
    [_% Win], 
    REMOVEFILTERS(sales_teams[regional_office], sales_teams[manager], sales_teams[sales_agent])
)

% of Wins for the selected manager's region:

_% Wins Region = 
VAR _Region = SELECTEDVALUE(sales_teams[regional_office])
VAR _Value = 
Calculate(
    [_% Win],
    sales_teams[regional_office] = _Region,
    REMOVEFILTERS(sales_teams[manager])
)
Return
IF(_Value = BLANK(), "-", _Value)

% of Wins for the selected manager's team:

_% Wins Team = 
VAR _Region = SELECTEDVALUE(sales_teams[regional_office])
VAR _Team = SELECTEDVALUE(sales_teams[manager])
VAR _Value = 
Calculate(
    [_% Win],
    sales_teams[regional_office] = _Region,
    sales_teams[manager] = _Team
)
Return
IF(_Value = BLANK(), "-", _Value)

I note the DAX might be a little overkill here, but would cover the case where a manager might have teams in different regions.

To add the dynamic context references, I employed a few measures that using variables and SWITCH(TRUE()). For example, calculating the % of Wins by the Team vs All Regions

_Perc of % Wins Team - All = 
VAR _Value =
    [_% Wins Team] - [_% Wins_All Regions]
RETURN
SWITCH(TRUE(),
_Value > 0 , "▲ " & FORMAT ( _Value, "+0.0%;-0.0%") & " vs. All Regions" ,
_Value < 0 , "▼ " & FORMAT ( _Value, "+0.0%;-0.0%") & " vs. All Regions" ,
_Value = 0, "No difference"
)

and for the Team vs the Team Region

_Perc of & Wins Team - Region = VAR _Value =
    [_% Wins Team] -  [_% Wins Region]
RETURN

SWITCH(TRUE(),
_Value > 0 , "▲ " & FORMAT ( _Value, "+0.0%;-0.0%") & " vs. Team Region" ,
_Value < 0 , "▼ " & FORMAT ( _Value, "+0.0%;-0.0%") & " vs. Team Region" ,
_Value = 0, "No difference"
)

Colour formatting was applied as an additional step based on whether the result was positive or negative, for example

_CF % Win Team - Region = 
VAR _Value = [_% Wins Team]- [_% Wins Region]
Return
SWITCH(TRUE(),
_Value > 0 , "#82b269",
_Value < 0 , "#c73d40",
"#aaaaaa")

Bar Chart

The Bar Charts were relatively straight forward. The order of all the charts were controlled by the bar chart showing the number of wins on the left hand side. Maintaining the order allows the names to be shown just once on the left hand side, and allows the manager to easily see the results for each individual and compare it against the others. If each metric was ordered differently, you would need to display names on each chart, and your eyes would be jumping up and down on each chart looking to find the agent you are focusing on....not ideal.

But, how did I do this? Well, the trick is to add the "No of Wins" measure as a tooltip on each bar chart. Then, by selecting the ellipsis at the top of the chart, you can sort the chart using that measure. Do this for all the charts, and everything will always be in order....

Now, I also employed some colour coding here to show the best and worst performers, using the traditional green and red. But I added a twist. If I had an employee that was maybe bottom of the pile in my team, but was actually better than the average of all employees across all the regions, I wouldn't want to give them a "red mark". So I added an additional amber colour to highlight that condition, using the following 3 x DAX measures:

_Max % Wins = 
VAR MaxValue = MAXX(ALLSELECTED(sales_teams[sales_agent]), [_% Win])
Return
IF(MaxValue = [_% Win], MaxValue, BLANK())

_Min % Wins = 
VAR MinValue = MINX(ALLSELECTED(sales_teams[sales_agent]), [_% Win])
Return
IF(MinValue = [_% Win], MinValue, BLANK())

_CF Max-Min % Wins = 
VAR MaxValue = [_Max % Wins]
VAR MinValue = [_Min % Wins]
VAR AllRegions = [_% Wins_All Regions]

Return

SWITCH(TRUE(),
[_% Win] = MaxValue, "#155d73",
[_% Win] = MinValue && [_% Win] < AllRegions , "#c73d40",
[_% Win] = MinValue && [_% Win] > AllRegions , "#f2be22",
"#aaaaaa")

For the Card and Bar Chart visuals, the DAX used for each metric was very similar. The main differences would be the format of the numbers (% or decimal or whole number) and for colour formatting whether large numbers were considered good or bad.

Product Analysis

This analysis may have been a little bit of an afterthought on my part, as initially I wanted to keep the report to a single page overview.

There are quite a few additional analyses I think could be done and may also be beneficial, but having time constraints, and also wanting to keep reports relatively simple and straightforward, I thought if I had to chose one, it would be sales performance based on products.

There would be a bit of a trade off here. In the previous pages, I was showing "everything everywhere all at once", but that would not be possible here, as I was combining metrics for a team across multiple products.

So I focused on looking at a single team, with a selected metric, and then showing the quarterly performance on a line chart, and then the product sales performance on a heat map/matrix.

The minor-hero here is the use of field parameters to allow the selection of a preferred metric, shown below.

When we have created such a parameter, which basically acts like a list of the parameters we want to switch between, it is a matter of just adding it to the visual in the same place you would add a measure. Then when you switch between measure types, the chart will automatically update.

One thing to remember is that if you want the formatting (colours, line types, etc) to act differently for each measure, you have to select each one, and format each version independently. It can be a bit of work to do it, but the end result is worth it. Always test each measure and combination to make sure it acts the way you wish!

End Result

So, there we go, a write up on the approach and execution of this report, which appears simple on the surface, but has quite a few moving parts under the hood!