Sum value by multiple dimensions in multiple tables

[DataScope06]

I have a data model with several tables. I need to sum the number of items ordered by product category for each customer and each year.

Tables listed here:

sales_items

OrderID	SKU	Qty_ordered
1	123	2
2	124	3
3	125	2
4	126	1
5	127	2
6	123	2

sales_order

OrderID	CustomerID	Order_Date
1	1	1/1/2020
2	2	6/1/2020
3	3	1/1/2021
4	1	8/1/2021
5	2	12/1/2021
6	1	2/1/2022

product

SKU	ProductCategory
123	Hats
124	Gloves
125	Shirts
126	Pants
127	Shoes

date

Date	Year	Month
1/1/2020	2020	January
6/1/2020	2020	June
1/1/2021	2021	January
8/1/2021	2021	August
12/1/2021	2021	December
2/1/2022	2022	February

Here is the expected output for CustomerID # 1. Note: I do not want this specific to customer #1, it needs to return this type of output for all Customer-Year-ProductCategory combinations. Customer #1 results are only shown as a brief example.

CustomerID	Year	ProductCategory	QtyOrdered
1	2020	Hats	2
1	2021	Pants	1
1	2022	Hats	2

[Anonymous · Accepted solution]

Hi @DataScope06 ,

I have created a simple sample, please refer to it to see if it helps you.

Create a measure.

Measure =
CALCULATE (
    MAX ( sales_items[Qty_ordered] ),
    FILTER (
        ALL ( 'product' ),
        'product'[SKU] = SELECTEDVALUE ( sales_items[SKU] )
    )
)

The year table has no relationship with other tables, so we cannot output the year column.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataScope06]

Thanks for the submission, @Anonymous . The year does have a relationship to other tables. The table, sales_order, has an order_date which joins to the date table. How can year then be tied in?

[Anonymous]

Hi @DataScope06 ,

You need to sum the number of items ordered by product category for each customer and each year. There are so many year, Who corresponds to which year? And there cannot create relationships among the date table and other tables.

Could you also provide the desired output with the pictures like the above?

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataScope06]

@Anonymous I just edited the question to make it more specific. I also added an example output.

[Anonymous]

Hi @DataScope06 ,

Could you please provide some sample data in the tables and the desired output with the sample data?

How to Get Your Question Answered Quickly 

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataScope06]

Hi @Anonymous, I added some data like you suggested.