ID 1 - Calculate Total Sales
SQL
SELECT
P.Category AS ProductCategory,
SUM(S.SalesAmount) AS TotalSales
FROM
Sales S
JOIN
Product P ON S.ProductKey = P.ProductKey
GROUP BY
P.Category
ORDER BY
TotalSales DESC;
DAX
DEFINE
MEASURE Sales[Total_Sales] = [Sales Amount]
EVALUATE
SUMMARIZE (
Sales,
'Product'[Category],
"TotalSales",
Sales[Total_Sales]
)
ORDER BY [TotalSales] DESC
Pandas
import pandas as pd
# Assuming you have loaded the Contoso data into DataFrames
# sales_df and product_df
# Merging sales and product DataFrames on ProductKey
merged_df = pd.merge(sales_df, product_df, on='ProductKey')
# Grouping by Product Category and summing the Sales Amount
total_sales_by_category = merged_df.groupby('Category')['SalesAmount'].sum().reset_index()
# Renaming columns for clarity
total_sales_by_category.columns = ['ProductCategory', 'TotalSales']
# Sorting by TotalSales in descending order
total_sales_by_category = total_sales_by_category.sort_values('TotalSales', ascending=False)
# Converting the result to a list of lists
result = total_sales_by_category.values.tolist()
print(result)
Output
| Electronics | 150000.0 |
| Furniture | 120000.0 |
| Clothing | 95000.0 |
| Accessories | 75000.0 |
Explanation
SUMMARIZE: This function groups the data and creates a summary table based on specified columns. In this case: Sales: The table to be summarized. 'Product'[Category]: The column from the Product table that will be used for grouping. This assumes that there is a relationship between Sales and Product tables, where Category is a column in the Product table. "TotalSales", Sales[Total_Sales]: This creates a new column in the summary table called "TotalSales", which will hold the aggregated total sales for each category. Sales[Total_Sales] refers to the measure defined earlier, which calculates the total sales amount.
YouTube Video Link
Leave a Comment