How to effectively showcase Sales Growth by Customer Name and Segments? Which customers had a High growth, an Average growth or Low growth?
To achieve this, I need measures for Total Sales and Total Sales Last Year. Then I need a measure to calculate the Sales Growth. For customer segmentation I need a supporting table with a column for the Growth names and a min and max column with predefined percentage thresholds.
Lastly, I need a measure for calculating the Sales per Growth Group followed by a Scatter chart which will enable to display all of this in an effective manner.
1 Calculate Total Sales, Sales LY and Sales Growth
Total Sales is calculated by multiplying the quantity for each product with the current price which in this case is in a different table than the Sales table hence the use of the RELATED function:
Total Sales =
SUMX( Sales,
Sales[Quantity] * RELATED( Products[Current Price] ) )
Calculating Sales LY can be done by using either DATEADD or SAMEPRIODLASTYEAR:
Sales LY = CALCULATE([Total Sales], DATEADD(Dates[Date], -1, YEAR))
To calculate the Sales Growth in percentage I use DIVIDE between Total Sales and Sales LY and type in -1:
Sales Growth = DIVIDE( [Total Sales], [Sales LY], 0 )-1
To check if the Sales Growth is calculated correctly, I drag in Customer Name to the canvas followed by Sales LY, Total Sales and Sales Growth measures. I also need to create a slicer for the Years.
2 Sales Growth Groups Table
Now I can create the Segmentation table. For example, let’s define High Growth as anywhere from 200% to 1000% then an Average Growth between a min of 30% to 200 % followed by a Poor Growth of negative 1000% to 30%.
I click on Enter Data, rename the table to Sales Growth Groups. Then I create the column headers. On the Min and Max columns I enter the numbers and format as percentage getting this final result:
3 Sales per Growth Group formula:
I start by typing in CALCULATE followed by the Total Sales measure. Now I type in FILTER to change the context for this calculation. Now I need a table manipulation function. VALUES returns a one-column table that contains the distinct values from the specified column. In this case I need to evaluate which rows remain based on the growth that each customer has had. So, I will type COUNTROWS and FILTER through the logic of the Sales Growth Groups inside the supporting table. Here I input Sales Growth greater than or equal to Min followed by double ampersand and less than Max. Lastly let’s round it off by zero.
Sales per Growth Group =
CALCULATE( [Total Sales],
FILTER( VALUES( Customers[Customer Name] ),
COUNTROWS(
FILTER( 'Sales Growth Groups',
[Sales Growth] >= 'Sales Growth Groups'[Min] &&
[Sales Growth] < 'Sales Growth Groups'[Max] ) ) > 0 ) )
//showing customer sales by growth segment
4 Scatter chart
Now it’s time to visualize this. I click on Scatter chart and drag Customer Name into Details. In the Legend I drag Customer Segments. For the X Axis I drag in the Sales per Growth Group measure and for the Y Axis I put Sales LY.
Last thing to enhance the visualization is to change the data colors to red for Low Growth, yellow for Average Growth and green for High Growth.
