Objective

Create an interactive dashboard that enables sales managers to track their team's quarterly performance.

Thought process

The main user of the report will be a sales manager

• They will want to see sales figures in quarterly buckets
• They will want to compare the performance of individual sales agents in their team

For the slicers I decided to group them and add dividers to show which slicers belong to each other (team, account and product based slicers)

For the metrics I wanted to show multiple things, so I ended up with 4 pages and 2 drillthrough pages (thesse 2 are identical in setup but I needed some DAX magic to make the drill through work with the Analysis Tool page)

Sales Performance

The top half of the screen is showing the main KPI:

• Year-to-Date numbers
• Sales Amount in quarterly buckets including delta versus previous quarter
• Closed opportunity info (number of Won & Lost and conversion rates per quarter)
• Open opportunities in the Engaging stage

At first I wanted to create measures for forecasted number of deals and sales amount for the next quarter but I noticed that there are many old opportunities in the dataset, so I opted to show the duration of opportunities in the Engaging stage instead:

Engagement Duration = 
var maxdate = CALCULATE(MAX(sales_pipeline[engage_date]), ALL(sales_pipeline))
RETURN
DATEDIFF(sales_pipeline[engage_date], maxdate, DAY) + 1

Then I compared this duration versus the Average, 80th percentile and 95th percentile of all closed deals in the dataset.

Opportunities with a duration longer then the 95th percentile are marked as ‘Opportunity at risk'

The bottom half of the screen is showing different quarterly metrics to quickly compare the performance between agents, achieved by layering them over each other and showing the relevant one based on the selected bookmark.

Pipeline

The top half is showing the team performance, showing the open and closed opportunity info from the first page again, to have a complete overview of the full pipeline.

The bottom half is showing 3 metrics split by sales agent.

Product

This page is showing a break-down per product for the main sales metrics. Relevant info is shown via bookmarks again.

The line graph in the bottom left corner, which is showing a line for each product, would become very cluttered when trying to show all products at once, so I’ve opted for a slicer on series to clean this visual up. This slicer is placed inside the graph, to make it clear that you will only filter that visual with this slicer, leaving all other visuals untouched.

Analysis Tool

My experience is that there are always questions which can’t be answered by the standard dashboards.

This page is an attempt to enable the managers to explore interesting differences accross multiple dimensions and for multiple measures, without ending up with a very cluttered report with too many visuals.

It is using 3 tables which are not linked to any other table in the model to show the dimensions and to select the measure.

There is 1 measure in the matrix visual that will show different calculations based on the selecting by the user (containing 3 nested SWITCH formulas and 229 lines of DAX code).

Drillthrough Page

On all visuals there is a drillthrough available to a detailed list of individual opportunities.

For the drillthrough from the Analysis Tool I needed a separate page to check for each opportunity id whether it corresponds with the selected dimensions in the slicers.