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!