ID 37 - Ranks customers by total sales grouped product

Max: 45
Try code in DAX.do
→

Syntax

ADDCOLUMNS ALL ALLEXCEPT CALCULATE CALCULATETABLE EXCEPT OR PRODUCT RANK RANKX SUM SUMMARIZE TOPN

Related

44 - Calculate Total Sales by Product Brand Over the Last 6 Months 40 - Filter Sales Based on Maximum Sales Amount from Specific Date 39 - Customers with Higher Total Sales than New York Customers 42 - Crossjoin to Count Products by Color and Category
◀

SQL

WITH CustomerSales AS (
    SELECT
        s.CustomerKey,
        c.Name AS CustomerName,
        p.Category AS ProductCategory,
        SUM(s.SalesAmount) AS TotalSales
    FROM Sales s
    JOIN Customer c ON s.CustomerKey = c.CustomerKey
    JOIN Product p ON s.ProductKey = p.ProductKey
    GROUP BY s.CustomerKey, c.Name, p.Category
),
TopCustomerSales AS (
    SELECT *
    FROM CustomerSales
    ORDER BY TotalSales DESC
    LIMIT 5
),
RankedCustomerSales AS (
    SELECT
        tcs.CustomerKey,
        tcs.CustomerName,
        tcs.ProductCategory,
        tcs.TotalSales,
        RANK() OVER (PARTITION BY tcs.ProductCategory ORDER BY tcs.TotalSales DESC) AS Ranking
    FROM TopCustomerSales tcs
)
SELECT *
FROM RankedCustomerSales
ORDER BY Ranking;

DAX

EVALUATE
VAR CustomerSales =
    SUMMARIZE (
        Sales,
        Sales[CustomerKey],
        Customer[Name],
        Product[Category],
        "TotalSales", Sales[Sales Amount]
    )

VAR TopCustomerSales =
    TOPN (
        5,
        CustomerSales,
        [TotalSales], DESC
    )

VAR RankedCustomerSales =
    ADDCOLUMNS (
        TopCustomerSales,
        "Ranking",
            RANKX (
                CALCULATETABLE (
                    CustomerSales,
                    ALLEXCEPT ( Sales, Product[Category] )
                ),
                [TotalSales],
                ,
                DESC
            )
    )

RETURN
    RankedCustomerSales
ORDER BY [Ranking]

Pandas

import pandas as pd

# Assuming sales_df, customer_df, and product_df are your dataframes
merged_df = sales_df.merge(customer_df, on="CustomerKey").merge(product_df, on="ProductKey")

# Summarizing customer sales by customer and product category
customer_sales = merged_df.groupby(['CustomerKey', 'Name', 'Category']).agg(
    TotalSales=('SalesAmount', 'sum')).reset_index()

# Selecting top 10 customers by total sales
top_customer_sales = customer_sales.nlargest(5, 'TotalSales')

# Ranking customers within each product category
top_customer_sales['Ranking'] = top_customer_sales.groupby('Category')['TotalSales'].rank(ascending=False, method='first')

# Sorting the result by ranking
top_customer_sales_sorted = top_customer_sales.sort_values('Ranking')

# Result
top_customer_sales_list = top_customer_sales_sorted[['CustomerKey', 'Name', 'Category', 'TotalSales', 'Ranking']].values.tolist()

Output

19088 Computers 137523.27 1
19089 Computers 130626.16 2
19084 Computers 153326.32 3
19087 Computers 215519.65 4
19037 Computers 127071.62 5

Explanation

CustomerSales: This variable summarizes sales by Customer[CustomerKey], Customer[Name], and Product[Category], calculating the total sales for each customer. TopCustomerSales: Takes the top 10 customers by their TotalSales. RankedCustomerSales: Adds a Ranking column based on the TotalSales for each customer within the same Product[Category]. The ALLEXCEPT function ensures the ranking is calculated only for customers within the same product category. RETURN: Returns the ranked top customer sales ordered by ranking.

Leave a Comment


Previous Next