Average Sales Variants — Tableau

In this blog, I will discuss average sales calculation. It seems simple to do average calculation but based on requirement there can be a few variants of the calculation. Let’s understand this by an example.

Tableau public version — 2021.4.2
Dataset — Sample — Superstore

The tableau workbook is published on tableau public and can be accessed at https://public.tableau.com/app/profile/jyoti.kumar7689/viz/DifferentSalesAverage/SalesinApril2020

Let’s look at the first sheet of the published workbook.

Sales in April 2020

We can look at the figure above, that shows sales for Category and Sub-Categories in April 2020 for each day. We can observe that all Sub-Categories are not sold everyday. There are two numbers in Grand Total. First is the sum of sales and second is the distinct count of order date. Hence, for Bookcases in Furniture, sales is 1558 and count of order date is 3 for April 2020.

How should be calculate average sales for a sub-categories in category for a period. It can be done in a few different ways based on the requirement.

• Average Sales for number of days sold — It can be defined as total sales for the period till date divided by count of days on which items were sold. e.g. For Bookcases in Furniture it is 1558/3 for April 2020.
• Average Sales for the period — It can be defined as total sales for the period till date divided by number of days in the period. e.g. For Bookcases in Furniture it is 1558/30 for April 2020. For a period of 1 to 15th April, 2020 it is 1300/2.
• Average Sales for the month — It can be defined as total sales for the period till date divided by number of days in the month in which we are considering the sales. e.g. For Bookcases in Furniture it is 1558/30 for April 2020.

All these calculations are done and presented in the second tab of the the published report.

Different Average Calculation

The measures used above are explained here:

Days in Selected Period

MTD

Sales by Days Sold

Days Selected Date

Sales Selected Day

Days in Month

Sales Days in Month

1. Days in Selected Period — Count of number of days the Sub-categories are sold till date in the period.
2. MTD — Sales in the month till date as selected using Selected Date parameter.
3. Sales by Days Sold — MTD/ Days in Selected Period
4. Days Selected Date — Number of days in the month as selected in the Selected Date parameter.
5. Sales Selected Day — MTD/ Days Selected Date
6. Days in Month — Number of the days in the month of the Selected Date.
7. Sales Days in Month — MTD/ Days in Month

Different averages have different relevance in the context of the business.

One can consider Sales Selected Day to understand the contribution of sales per day irrespective of the fact that the category is sold all days in the period or not.

Similarly, One would look for Sales Days in Month to understand the contribution of Average Sales done till date in the month to the monthly average sales.

I have made an attempt to discuss a few variants of the average sales calculation that we may encounter based on the business requirement.

Please share the blog if you like it.