This Power BI report provides a sales analysis for our stakeholders, including a business summary, basket analysis and customer analysis to provide insights into our sales.

See bottom of page for additional images, including model view.

Business Summary

The business summary page in this report provides an overall summary of our sales, including:

Total sales

Total Sales = 
SUMX(Sales_Data,
    Sales_Data[Order Quantity] * Sales_Data[Unit Price])

MoM Growth

MoM Revenue Growth Rate = 
VAR lastmonthSales = CALCULATE([Total Sales], DATEADD(Dates[Date], -1, MONTH))

RETURN
DIVIDE( [Total Sales]-lastmonthSales, lastmonthSales, 0)

Gross profit

Total Profits = [Total Sales] - [Total Cost]

Total customers

Total Customers = 
COUNTROWS(VALUES(Sales_Data[Customer Name Index]))

Top 3 cities by sales

To return the value for the top 3 best selling cities, I used TOPN. This returns the value in sales for the top 3 cities, then I combined this with the cities fields to show the cities and their respective values.

Top 3 Best Selling Cities = 
VAR Cities = VALUES(Regions_Table[City])

RETURN
CALCULATE( [Total Sales],
    TOPN(3, ALL(Regions_Table[City]), [Total Sales],DESC),
        Cities )

Units sold

Total Quantity Sold = SUM(Sales_Data[Order Quantity])

Cumulative sales (cumulative sales, cumulative sales LM, cumulative sales 2 months ago)

Cumulative Sales = 
CALCULATE( [Total Sales],
FILTER( ALLSELECTED( Dates),
Dates[Date] <= MAX(Dates[Date])))

For the cumulative sales LM, I used measure branching by first creating a measure for sales last month, then I applied the same logic as above for cumulative sales, swapping the 'Total Sales' for 'Sales Last Month'

Sales Last Month = 
CALCULATE(
    [Total Sales],
    DATEADD(Dates[Date], -1, MONTH))

Cumulative Sales LM = 
CALCULATE( [Sales Last Month],
FILTER( ALLSELECTED( Dates),
Dates[Date] <= MAX(Dates[Date])))

Again, for the cumulative sales 2 months ago, I used measure branching. First I created a measure for sales 2 months ago, then I used this measure in my calculate formula.

Sales 2 Months ago = 
CALCULATE(
    [Total Sales],
    DATEADD(Dates[Date], -2, MONTH))

Cumulative Sales 2 Months ago = 
CALCULATE( [Sales 2 Months ago],
FILTER( ALLSELECTED( Dates),
Dates[Date] <= MAX(Dates[Date])))

Basket Analysis

The basket analysis page in this report provides a deep dive into our customers' buying patterns e.g. cross selling, best/worst selling products and valuable insights such as average order value: -

Total Sales

Total Sales = 
SUMX(Sales_Data,
    Sales_Data[Order Quantity] * Sales_Data[Unit Price])

Total Transactions

Total Transactions = COUNTROWS(Sales_Data)

Average Order Value

Average Order Value (AOV) = 
COALESCE(
DIVIDE( [Total Sales], [Total Transactions], 0) 
,0)

Top 5 Products

Top 5 Products = 
VAR Products = VALUES(Products_Data[Product Name])

RETURN
CALCULATE( [Total Sales],
    TOPN(5, ALL(Products_Data[Product Name]), [Total Sales],DESC),
        Products)

Worst 5 Products

Worst 5 Products = 
VAR Products = VALUES(Products_Data[Product Name])

RETURN
CALCULATE( [Total Sales],
    TOPN(5, ALL(Products_Data[Product Name]), [Total Sales],ASC),
        Products)

Customers who bought (selected product)

Customers Who Bought = 
IF( 
    SELECTEDVALUE( Filter_Products_Data[Filter_Index]) = SELECTEDVALUE( Sales_Data[Product Description Index]),
     BLANK(),
DISTINCTCOUNT(Sales_Data[Customer Name Index]))

Customers who bought both products

Customers with Both Products = 
VAR BothProducts = 
CALCULATETABLE( VALUES( Sales_Data[Customer Name Index]),
ALL( Products_Data),
USERELATIONSHIP(Sales_Data[Product Description Index], Filter_Products_Data[Filter_Index]))

RETURN
CALCULATE( [Customers Who Bought], BothProducts)

Cross Selling %

Cross Selling % = 
DIVIDE( [Customers with Both Products], [Customers Who Bought], 0 )

Customer Analysis

The customer analysis page in this report provides a thorough analysis of customer segmentation, this could be useful for targeting customers with discounts, rewards etc to maximise sales and keep customers returning. Useful insights include:

Total customers

Total Customers = 
COUNTROWS(VALUES(Sales_Data[Customer Name Index]))

Repeat customers (from last month)

Customers That Ordered Last Month and This Month (INTERSECT) = 
COALESCE(
    COUNTROWS(INTERSECT(
    VALUES(Sales_Data[Customer Name Index]),
    CALCULATETABLE( VALUES( Sales_Data[Customer Name Index]), DATEADD(Dates[Date], -1, MONTH)))),
    0)

Customers who didn't order last month

Customers That Didnt Order Last Month (Except) = 
COALESCE(
    COUNTROWS(EXCEPT(
    VALUES(Sales_Data[Customer Name Index]),
    CALCULATETABLE( VALUES( Sales_Data[Customer Name Index]), DATEADD(Dates[Date], -1, MONTH))))
    ,0)

New customers

New Customers = 
VAR Customers = VALUES(Sales_Data[Customer Name Index])

RETURN 
CALCULATE([Total Customers],
    FILTER( Customers,
    CALCULATE( COUNTROWS( Sales_Data),
        FILTER( ALLSELECTED( Dates ), Dates[Date] < MIN( Dates[Date] ) ) ) = 0 ) )

Returning customers

Returning Customers = [Total Customers] - [New Customers]

New customer sales

New Customer Sales = 
VAR Customers = VALUES(Sales_Data[Customer Name Index])

RETURN 
CALCULATE([Total Sales],
    FILTER( Customers,
    CALCULATE( COUNTROWS( Sales_Data),
        FILTER( ALLSELECTED( Dates ), Dates[Date] < MIN( Dates[Date] ) ) ) = 0 ) )

Returning customer sales

Returning Customer Sales = [Total Sales] - [New Customer Sales]