SELECT
C.CustomerName,
SUM(S.SalesAmount) AS TotalSales
FROM
Sales S
JOIN
Customer C ON S.CustomerKey = C.CustomerKey
GROUP BY
C.CustomerName
ORDER BY
TotalSales DESC;
DEFINE
MEASURE Sales[Total_sales] = [Sales Amount]
EVALUATE
SUMMARIZECOLUMNS (
Customer[Name],
SELECTCOLUMNS (
FILTER ( Customer, Customer[Name] <> BLANK () ),
"CustomerName", Customer[Name]
),
"TotalSales", [Total_sales]
)
ORDER BY [Total_sales] DESC
import pandas as pd
# Assuming you have loaded the Contoso data into DataFrames
# sales_df and customer_df
# Merging sales and customer DataFrames on CustomerKey
merged_df = pd.merge(sales_df, customer_df, on='CustomerKey')
# Grouping by CustomerName and summing the SalesAmount
total_sales_by_customer = merged_df.groupby('CustomerName')['SalesAmount'].sum().reset_index()
# Sorting by TotalSales in descending order
total_sales_by_customer = total_sales_by_customer.sort_values('SalesAmount', ascending=False)
# Renaming columns for clarity
total_sales_by_customer.columns = ['CustomerName', 'TotalSales']
# Converting the result to a list of lists
result = total_sales_by_customer.values.tolist()
print(result)
| John Doe | 9500.0 |
| Jane Smith | 8700.0 |
| Alice Johnson | 8100.0 |
| Bob Brown | 7800.0 |
DEFINE: MEASURE Sales[Total_sales] = [Sales Amount] creates a measure named Total_sales that sums the Sales Amount from the Sales table. SUMMARIZECOLUMNS: SUMMARIZECOLUMNS: This function is used to create a summary table. In your query, it groups data by the Customer[Name] and calculates the Total_sales for each customer. SELECTCOLUMNS with FILTER: SELECTCOLUMNS: Creates a virtual table with just the CustomerName column from the Customer table. FILTER ( Customer, Customer[Name] <> BLANK () ): Filters the Customer table to exclude any rows where Customer[Name] is blank (or null). "CustomerName", Customer[Name]: Specifies the new column name "CustomerName" and the source column Customer[Name]. "TotalSales", [Total_sales]: This creates a new column called "TotalSales" in the resulting table using the Total_sales measure. ORDER BY [Total_sales] DESC: Orders the summarized data by Total_sales in descending order, so the customers with the highest sales appear first.
Leave a Comment