SELECT *
FROM customer
WHERE total_sales > ALL
(SELECT total_sales
FROM customer
WHERE city = 'New York');
EVALUATE
VAR NewYorkSales =
CALCULATETABLE (
SUMMARIZE (
Customer,
Customer[CustomerKey],
"TotalSales", SUMX (RELATEDTABLE(Sales), Sales[Sales Amount])
),
Customer[City] = "New York"
)
VAR Result =
FILTER (
Customer,
CALCULATE (SUMX (RELATEDTABLE(Sales), Sales[Sales Amount])) >
MAXX (NewYorkSales, [TotalSales])
)
RETURN Result
import pandas as pd
# Assuming `customers` and `sales` DataFrames are already defined
new_york_sales = sales[sales['City'] == 'New York'].groupby('CustomerKey')['Sales Amount'].sum().max()
higher_sales_customers = customers.merge(
sales.groupby('CustomerKey')['Sales Amount'].sum().reset_index(),
on='CustomerKey'
)
result = higher_sales_customers[higher_sales_customers['Sales Amount'] > new_york_sales]
# Display the result
result[['CustomerKey', 'Name', 'City', 'Sales Amount']]
| 10234 | Smith, John | Los Angeles | 50000 |
| 10987 | Doe, Jane | San Francisco | 45000 |
| 10567 | Lee, Kevin | Chicago | 52000 |
1.NewYorkCustomers Calculation: FILTER ( Customer, Customer[City] = "New York" ): This part filters the Customer table to get only the customers from New York. SUMMARIZE ( ..., "TotalSales", CALCULATE ( SUM ( Sales[Sales Amount] ) ) ): For each New York customer, it calculates their total sales by summing the Sales[Sales Amount] for that customer. 2.MaxNewYorkSales Calculation: MAXX ( NewYorkCustomers, [TotalSales] ): From the list of New York customers, this calculates the maximum total sales. This is the highest sales amount made by any New York customer. 3.Filtering the Customers: ADDCOLUMNS ( Customer, "TotalSales", CALCULATE ( SUM ( Sales[Sales Amount] ) ) ): For every customer in the Customer table, this adds a new column called TotalSales, which calculates their total sales amount. FILTER ( ..., [TotalSales] > MaxNewYorkSales ): The FILTER function then selects only those customers whose total sales are greater than the maximum sales of New York customers (calculated in step 2). 4.Returning the Result: The RETURN statement produces a table of customers whose total sales are higher than the highest total sales from New York customers, sorted by their total sales amount.
Leave a Comment