Business Dashboards: Pivots, DAX and Charts
Visualizing Business Data in Excel; Data is the lifeblood of a modern business. Let’s create sales, customer and product dashboards for business optimization.
Growing a business in today’s world needs in-depth knowledge of key metrics like how much you make (revenue), who’s your best customer (lifetime value) and what your customers love (bestsellers), to name a few. Collecting and analyzing your business data is the best way to optimize your business’ growth in a way that takes full advantage of the insights from these metrics.
Top-level business stakeholders need to manage their time, so these sort of insights need to be shared with them in a concise and efficient manner; enter Dashboards! Data dashboards are dynamic and interactive tools used to track, analyze and share Key Performance Indicators (KPIs) and metrics.
So how do you build one that answers the right business questions? The first step is to understand the purpose of the dashboard, then decide on what data to collect and how to get it, before you begin the data analysis process.
Working as a business analyst over the years for a retail brand, I helped grow the company from a start-up without a database to an international brand that has leveraged their sales, product and customer data to launch several international outlets, leading to a ten-fold increase in annual revenue.
How? I introduced a robust Point-of-Sale (POS) platform with a detailed analytics feature to facilitate the holistic collection of sales, customer and inventory data which I used in building a dynamic dashboard for the management team, to guide their business decision-making.
In line with the stakeholders’ requirements, I created 3 dashboards with Excel that answer their business questions on Sales, Customers and Products for each outlet. Below is a table of the key insights requested:
These 3 dashboards are the templates for this data story, however I modified the data values to maintain data privacy and subset to a 3-year time period, from 2021–2023. The modified Excel files for the Sales and Product dashboards are available on my github here and here. Now we know the business questions, let’s get into how I built the dashboards in Excel.
The dashboards are interactive and all filter buttons work accordingly. You can filter by outlet, year, customer account status (registration), customer segments or any other filters you prefer. Let’s begin with the Sales Dashboard.
How to Create the Sales Dashboard
The first step is data cleaning. After downloading the sales data from the POS portal, I opened and cleaned the data in Excel; removing duplicates, changing data formats, transposing columns and joining rows with VLOOKUP.
After cleaning, the next step is to link your worksheet table to a data model in order to create your pivot tables and KPIs from a single source. To link your data, go to Power Pivot > Add to Data Model, then select your data range (ensure you tick my data has headers). A linked data model will be created with your data and will automatically update to any changes made in the worksheet table.
I proceeded to creating the pivot tables for each sales metric with data from the linked data model. The pivot tables were then used to output pivot charts which are key elements of the dashboard. These charts will be pasted into the dashboard page and organized elegantly.
The bulk of the work in making an interactive sales dashboard with Excel is in creating the KPI labels. This is where Power Pivot comes in. Make sure that you’ve linked your worksheet table to a data model, as a Measure or KPI can only be created from a data model.
Once the Power Pivot window opens the data table, you can begin to create Measures (calculated fields) with Data Analysis Expressions or DAX. DAX is a formula language that is used to define calculations for calculated columns or measures.
In the data model’s window the bottom cells after the table are where Measures are stored, so you can select a cell, then click on Measures > New Measures to create one. For example, here are some of my DAX formulas to calculate measures in the sales dashboard:
Total Revenue:
=SUM([revenue])
Average Basket Size
=AVERAGE([basket_size])
Total Customer Visits
=SUM([customer_count])
Highest Revenue
=MAX([revenue])When you save the Measure it becomes available for use in creating a KPI label using the CUBEVALUE function. To do this, return to the worksheet, click on a cell and enter the needed formula. Note that if you intend to use slicers in your dashboard to filter these labels, you need to create these first so they can be added to your CUBEVALUE formula. This action connects a Measure to the slicer for filtering purposes.
To create a slicer, simply go to Insert > Slicer. In the Existing Connections window that displays, select the Data Model tab and choose the table that exists in your model. Once the slicer is created, right-click on it and select Slicer Settings to see the slicer’s name. This name will be used in the CUBEVALUE formula. Below are the matching formulas for some of the measures listed earlier:
Total Revenue:
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Revenue]",Slicer_outlet2, Slicer_year1)
Average Basket Size:
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Average Basket Size]",Slicer_outlet2, Slicer_year1)The CUBEVALUE formula automatically detects your data model, so it begins with “ThisWorkbookDataModel”, then you state the Measure, then state the slicer(s) name(s). The formula then returns the calculated value for the Measure which can be filtered using the linked slicers.
It’s important to define a name for each cell containing a CUBEVALUE formula (i.e. a Measure), and we’ll know why in a minute. To name a cell, click the Name box at the left end of the formula bar, then type the name you want to use to refer to the cell. For example, the cell containing the calculation for the Total Revenue measure can be named total_revenue.
Repeat this for all your measures’ cells then create and design text boxes for each KPI label. To insert a measure into the textbox, simply type an equal sign, then state the name you gave the cell containing your desired Measure (for example, =total_revenue). This will insert that Measure’s value into the text box.
All that’s left is choosing your fonts, textbox design and dashboard elements’ placements. You can have a look at several dashboard designs online for inspiration. Here’s a gif of my worksheet for the sales dashboard:
And your dashboard’s ready! Below is a gif of my sales dashboard in action. It’s worthy to note that Power Pivot also has the ability to create KPIs with preset design options, but I choose to go the Measures and text box route as this gives me more design freedom.
The beauty of using pivot charts as dashboard elements in Excel is that they have built-in filters (shown below) which you can use or hide within the chart. I also enabled the year and outlet slicers to control all the pivot charts together, so we can filter by year or outlet for all charts and KPIs simultaneously. To do this, right-click on each slicer you created earlier and select Report Connections, then tick all the pivot tables you want connected to the slicer. Ensure that you enable all the pivot tables your charts were created from.
Creating the Customer Dashboard
The customer dashboard was created with the same process as the sales one, with some minor differences. During data cleaning, I used IF statements on the customer revenue column to create account status (registered or walk in) and customer profitability segments; customers with Lifetime Value (LTV) less than $500K were grouped in the Low Profit segment (remember it’s dummy data!), customers with LTV between $500,001 and $1M were grouped as Average while those with over $1.5M lifetime purchase value were placed in the High Profit segment. Also, the DAX formulae used to calculate the Measures here required a bit more thought. Here are a few of them:
Highest number of items bought by a single customer
=MAXX(FILTER(data_cleaned, data_cleaned[customer]<>"Walk In"),[items_sold])
Percentage of customers with over 1 visit
=(COUNTX(FILTER(data_cleaned, data_cleaned[items_sold]>1),[items_sold])/COUNT(data_cleaned[items_sold])) * 100
Total revenue from registered customers
=SUMX(FILTER(data_cleaned, data_cleaned[registration]="Registered"),[customer_revenue])
Total transactions by walk in customers
=SUMX(FILTER(data_cleaned, data_cleaned[registration]="Walk In"),[transaction_count])In this dashboard, I visualized the top customers by revenue, visits and purchases in three profile boxes after identifying their identities from pivot tables. Key stats for each customer were pulled from the company database and input in the text boxes. Here’s an image of the Customer Dashboard.
All requested customer metrics were provided in the dashboard, with an added ability to filter data not just by year and outlet, but also by customer segments and account (registration) status in relevant charts. Here’s a gif of the customer dashboard below:
Creating the Product Dashboard
The major challenge in creating this dashboard was data preparation and cleaning; monthly data had to be collected for each outlet per year and joined to the annual data table.
During data cleaning, I created a pivot table of total units sold for each product, copied out the data to another sheet and used IF statements on the total units sold column to create product demand segments; products with over 2000 total units sold were grouped in the High Demand segment, products with total unit sales between 701 and 2000 units were grouped as Regular Demand, those with total sales between 201 and 700 units were grouped in the Average Segment while products with less than 200 units sold were placed in the Low Demand segment. VLOOKUP was then used to match the segments to the products in the main data table, thus creating the demand field.
I also used VLOOKUP to match the product rows to their designated category (Face, Body or Face & Body), as well as their corresponding type (Cleanser, Lotion, Cream etc), giving us the category and type fields. Here’s a gif of my worksheets for the products dashboard:
The DAX formulas used to calculate the Measures here were more advanced. Here are some of them:
percent_sold_face
Percentage of sold products in Face category
(SUMX(FILTER(Table1, Table1[category]="Face"),[items_sold])/SUM(Table1[items_sold])) * 100
most_valuable
Product with highest revenue
=FIRSTNONBLANK ( TOPN ( 1, VALUES ( Table1[product] ), CALCULATE ( SUM ( Table1[revenue] ) ) ), 1 )
most_valuable_body
Body product with highest revenue
CALCULATE(FIRSTNONBLANK ( TOPN ( 1, VALUES ( Table1[product] ), CALCULATE ( SUM ( Table1[revenue] ) ) ), 1 ), FILTER(Table1,Table1[category]="Body"))
most_purchased
Product with most units sold
FIRSTNONBLANK ( TOPN ( 1, VALUES ( Table1[product] ), CALCULATE ( SUM ( Table1[items_sold] ) ) ), 1 )
most_purchased_face
Face product with most units sold
CALCULATE(FIRSTNONBLANK ( TOPN ( 1, VALUES ( Table1[product] ), CALCULATE ( SUM ( Table1[items_sold] ) ) ), 1 ), FILTER(Table1,Table1[category]="Face"))
In this dashboard, I visualized the top products by revenue and demand in three profile boxes after identifying their identities from pivot tables. Key stats from the three-year period for each top product were input in text boxes. Here’s an image of the Product Dashboard.
All requested product metrics were provided in the dashboard, with an added ability to filter data not just by year and outlet, but also by month and product category in relevant charts. Here’s a gif of the product dashboard below:
Now we have the dashboards, we can update the data by simply adding new values to the worksheet’s table which is linked to the data model, and the model will update automatically to include these new values. You should also refresh and update your pivot tables to include the new data so the dashboards stay up-to-date.
With these dashboards’ insights, managers can make effective decisions on what outlets to stock more inventory at (outlets generating the most revenue), see which outlets are under-performing and find out why, contact the top customers and offer deals that keep them coming, encourage the outlets with the most walk-in customers to improve on account registrations, drive ads towards bestsellers and cut dead stock etc. The possibilities are endless with great dashboards.
Stay tuned and remember to check out my github for the dashboard files and view my portfolio for more data stories.
