SELECT
P.Color,
P.Category,
COUNT(*) AS "#Prods"
FROM
Product AS P
WHERE
P.Category IN ('TV and Video', 'Computers')
GROUP BY
P.Color, P.Category
ORDER BY
P.Category, P.Color;
EVALUATE
CALCULATETABLE (
ADDCOLUMNS (
CROSSJOIN (
VALUES ( 'Product'[Color] ),
VALUES ( 'Product'[Category] )
),
"#Prods", CALCULATE ( COUNTROWS ( 'Product' ) )
),
'Product'[Category] IN { "TV and Video", "Computers" }
)
ORDER BY [Category], [Color]
import pandas as pd
# Assuming df is your DataFrame for the Product table
df_filtered = df[df['Category'].isin(['TV and Video', 'Computers'])]
# Group by Color and Category, then count the number of rows in each group
result = df_filtered.groupby(['Color', 'Category']).size().reset_index(name='#Prods')
# Sort the result by Category and Color
result_sorted = result.sort_values(by=['Category', 'Color'])
| Black | Computers | 12 |
| Gray | Computers | 8 |
| White | Computers | 15 |
| Black | TV and Video | 7 |
| Gray | TV and Video | 10 |
| White | TV and Video | 5 |
CROSSJOIN: Combines the distinct values of 'Product'[Color] and 'Product'[Category] to generate all possible combinations of these two attributes. ADDONS: Adds a calculated column #Prods, which represents the number of products for each combination of color and category. CALCULATE: Used to count the number of rows (COUNTROWS) in the 'Product' table for each combination. CALCULATETABLE: Filters the product categories to include only "TV and Video" and "Computers." ORDER BY: Sorts the result by category first and then by color.
Leave a Comment