Analysing Customer Sales by RANKX() and “What If” Parameters

This post aims to show how to use a combination of the RANKX() function and the Power BI “What-If” parameters to analyse monthly sales to find out which customer has the highest, lowest, and average sales/spending growth. The formula used to calculate the sales trend is: the margin of the current month sales to the average sales amount during the last 12 months. See below:

Customer Sales Trend = (current month sales – average sales over last 12 months) / average sales over the last 12 months

By displaying CustomerName and SalesTrend% in a table visual the end users are able to:

  • Toggle between best and worst customers,
  • Change the number of the visible customers in the visualisation (top/bottom N customers),
  • Change the percentage boundary to show the middle customers (e.g. from -100% to 100% , -70% to 70%).

See examples of these below:

The model used for this demo is a simple Sales dataset with 3 tables: Sales, Date and Customer:

First, a measure is created to show the monthly average of sales during the 12 months prior to the selected month. This is done by using AverageX and DatesInPeriod functions:

Sales Amount Avg L12M =CALCULATE(AVERAGEX(VALUES(‘Date'[Month]), Sales[SalesAmount])DATESINPERIOD(‘Date'[Date], DATEADD(FIRSTDATE(‘Date'[Date]),-1,DAY), -12, MONTH))

Then the Sales Trend % can be calculated as margin of Sales Amount to the 12 months average:

Sales Trend% = DIVIDE(Sales[Sales Amount]-Sales[Sales Amount Avg L12M], Sales[Sales Amount Avg L12M], BLANK())

Two What-If parameters are created: one for selecting the number of visible customers:

And one for changing boundaries of SalesTrend %:

A table with one column and two rows must be created to store top and bottom values, which are used for toggling:

Two slicers are created for the What-If parameters and a ChicleSlicer (a custom visual) for Top-Bottom based on Direction column in a ShowType table:

To create measures to show Top/Bottom customers, first start with a simple formula and develop it step by step by including parameters.

The below DAX calculation returns the top 20 customers with the highest sales trend:

Sales Trend% Top Customers =CALCULATE (Sales[Sales Trend%],FILTER (VALUES ( Customer[Customer Name] ),RANKX (ALL ( Customer[Customer Name] ),Sales[Sales Trend%], , DESC) <= 20))

Credit to Sam McKay from Enterprise DNA for the above formula.

By replacing “20” in the above formula with CustomersToShow[CustomersToShow Value], the number of visible customers changes dynamically based on the CustomerToShow parameter:

Sales Trend% Top Customers = CALCULATE (Sales[Sales Trend%],FILTER (VALUES ( Customer[Customer Name] ),RANKX (ALL ( Customer[Customer Name] ),Sales[Sales Trend%], , DESC) <= CustomersToShow[CustomersToShow Value] ))

And to show the top customers in a selected sales trend percentage range, Customer[Customer Name] must be filtered inside RANKX and in the result set:

Sales Trend% Top Customers = CALCULATE (Sales[Sales Trend%], FILTER( VALUES ( Customer[Customer Name] ), RANKX (FILTER (ALL(Customer[Customer Name] ), Sales[Sales Trend%] >=MIN(‘SalesTrendBondary'[SalesTrendBondary] )&& Sales[Sales Trend%] <= MAX ( ‘SalesTrendBondary'[SalesTrendBondary] )),Sales[Sales Trend%], , DESC ) <= CustomersToShow[CustomersToShow Value] && Sales[Sales Trend%] >= MIN ( ‘SalesTrendBondary'[SalesTrendBondary] ) &&Sales[Sales Trend%] <= MAX ( ‘SalesTrendBondary'[SalesTrendBondary] )))

To create another measure for bottom customers do similar to the above measure except the RANKX function DESC should be replaced by ASC:

Sales Trend% Bottom Customers = CALCULATE (Sales[Sales Trend%], FILTER( VALUES ( Customer[Customer Name] ), RANKX (FILTER (ALL(Customer[Customer Name] ), Sales[Sales Trend%] >=MIN(‘SalesTrendBondary'[SalesTrendBondary] )&& Sales[Sales Trend%] <= MAX ( ‘SalesTrendBondary'[SalesTrendBondary] )),Sales[Sales Trend%], , ASC ) <= CustomersToShow[CustomersToShow Value] && Sales[Sales Trend%] >= MIN ( ‘SalesTrendBondary'[SalesTrendBondary] ) &&Sales[Sales Trend%] <= MAX ( ‘SalesTrendBondary'[SalesTrendBondary] ) ))

Then create a measure to switch between top and bottom measures based on ShowType[Direction] value:

Sales Trend% Customer = IF(SELECTEDVALUE(ShowType[Direction])=”Top”,Sales[Sales Trend% Top Customers],Sales[Sales Trend% Bottom Customers])

All calculations are completed! [Customer Name] and [Sales Trend% Customer] are added to the table visual can be explored using the slicers to see top, bottom, and average customers of the month:

This article was written by Ali Sharifi from Agile Analytics – read more of his great tips and insights on his blog!

By |2019-10-24T10:31:14+10:00October 24th, 2019|Tech Insights & Tricks|0 Comments

About the Author:

Agile Analytics is a boutique consulting firm and a Microsoft Gold Partner in Data Platform and Data Analytics. At Agile Analytics, we consult, design and deliver innovative data analytics solutions that help you gain and sustain competitive advantage through data-driven culture.

Leave A Comment