ID 31 - Calculate the Average Sales Amount for Each Customer with Sales Above a Certain Threshold

Max: 45
Try code in DAX.do
→

Syntax

ADDCOLUMNS AVERAGE AVERAGEX CALCULATE FILTER MAX OR SUM TOPN VALUE VALUES

Related

45 - 3-Year Moving Average of Sales
◀

SQL

WITH CustomerSales AS (
    SELECT
        c.CustomerKey,
        c.Name AS CustomerName,
        SUM(s.SalesAmount) AS TotalSales,
        AVG(s.SalesAmount) AS AverageSales
    FROM Sales s
    JOIN Customer c ON s.CustomerKey = c.CustomerKey
    GROUP BY c.CustomerKey, c.Name
)
SELECT
    CustomerKey,
    CustomerName,
    TotalSales,
    AverageSales
FROM CustomerSales
WHERE TotalSales > 10000
ORDER BY AverageSales DESC
LIMIT 3;

DAX

DEFINE
    MEASURE Sales[Total_Sales] = SUM(Sales[Sales Amount])
    MEASURE Sales[Average_Sales] =
        AVERAGEX ( Sales, Sales[Sales Amount] )

EVALUATE
TOPN (
    3,
    FILTER (
        ADDCOLUMNS (
            VALUES ( Customer[CustomerKey] ),
            "CustomerName", CALCULATE (MAX(Customer[Name])),
            "TotalSales", [Total_Sales],
            "AverageSales", [Average_Sales]
        ),
        [TotalSales] > 10000
    ),
    [AverageSales], DESC
)
ORDER BY [AverageSales] DESC

Pandas

import pandas as pd

# Assuming df_sales and df_customer are your DataFrames


# df_sales = pd.read_csv('sales.csv')
# df_customer = pd.read_csv('customer.csv')

# Merge Sales and Customer DataFrames on CustomerKey
merged_df = pd.merge(df_sales, df_customer, on='CustomerKey')

# Group by CustomerKey and Name, and calculate TotalSales and AverageSales
grouped_df = merged_df.groupby(['CustomerKey', 'Name']).agg(
    TotalSales=('SalesAmount', 'sum'),
    AverageSales=('SalesAmount', 'mean')
).reset_index()

# Filter customers with TotalSales greater than 10,000
filtered_df = grouped_df[grouped_df['TotalSales'] > 10000]

# Get the top 3 customers based on AverageSales
top_3_customers = filtered_df.sort_values(by='AverageSales', ascending=False).head(3)

print(top_3_customers)

Output

1124 Liang, Wesley 12799.96 12799.96
5846 Adams, Andrea 10077.6 10077.6
15412 Xu, Mario 11369.28 5684.64

Explanation

Define Measures: Sales[Total_Sales]: This measure calculates the total sales amount for the current context (i.e., for each customer). Sales[Average_Sales]: This measure calculates the average sales amount over all sales rows. It uses AVERAGEX, iterating over each row in the Sales table and averaging the Sales Amount. EVALUATE: TOPN(3, ...): Retrieves the top 3 customers based on AverageSales in descending order. FILTER(...): Filters the data to include only customers with TotalSales greater than 10,000. ADDCOLUMNS(...): Adds columns to the customer data, including: CustomerName: The name of the customer. TotalSales: The total sales amount for each customer. AverageSales: The average sales amount for each customer. ORDER BY: Orders the results by AverageSales in descending order

Leave a Comment


Previous Next