ID 33 - customer-product combinations that have maximum sales in the Sales table

Max: 45
Try code in DAX.do
→

Syntax

COUNT COUNTROWS FILTER MAX MAXX PRODUCT ROW SUM SUMMARIZE SUMMARIZECOLUMNS

Related

40 - Filter Sales Based on Maximum Sales Amount from Specific Date 39 - Customers with Higher Total Sales than New York Customers
◀

SQL

WITH SalesSummary AS (
    SELECT
        CustomerKey,
        ProductKey,
        COUNT(*) AS SalesCount
    FROM Sales
    GROUP BY CustomerKey, ProductKey
)
SELECT
    CustomerKey,
    ProductKey,
    SalesCount
FROM SalesSummary
WHERE SalesCount = (
    SELECT MAX(SalesCount)
    FROM SalesSummary
);

DAX

EVALUATE
VAR SalesSummary = 
    SUMMARIZECOLUMNS (
        Sales[CustomerKey],
        Sales[ProductKey],
        "SalesCount", COUNTROWS ( Sales )
    )
VAR MaxSalesCount = 
    MAXX(SalesSummary, [SalesCount])
RETURN
FILTER (
    SalesSummary,
    [SalesCount] = MaxSalesCount
)

Pandas

import pandas as pd

# Assuming df_sales contains the Sales data with CustomerKey and ProductKey
df_sales = pd.DataFrame({
    'CustomerKey': [1, 2, 1, 3, 2, 1, 3, 3],
    'ProductKey': [100, 101, 100, 102, 101, 103, 102, 102]
})

# Group by CustomerKey and ProductKey, calculate SalesCount
grouped_sales = df_sales.groupby(['CustomerKey', 'ProductKey']).size().reset_index(name='SalesCount')

# Get the maximum SalesCount
max_sales_count = grouped_sales['SalesCount'].max()

# Filter the rows where SalesCount equals the maximum
result = grouped_sales[grouped_sales['SalesCount'] == max_sales_count]

# Convert to list of lists format
result_list = result[['CustomerKey', 'ProductKey', 'SalesCount']].values.tolist()

Output

3 102 3

Explanation

SQL: The query calculates the total sales count for each customer-product combination, identifies the maximum SalesCount, and filters the result to show only the combination with the highest SalesCount.\n DAX: The logic is similar to SQL, where SUMMARIZECOLUMNS groups the data, MAXX calculates the highest SalesCount, and FILTER extracts only the rows that match the maximum value. Pandas: Grouping by CustomerKey and ProductKey, we count the number of occurrences for each combination and filter out the maximum value of the SalesCount.

Leave a Comment


Previous Next