Forum Discussion
Sum value by multiple dimensions in multiple tables
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 |
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.
6 Replies
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.
Hi Anonymous, I added some data like you suggested.
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.
Anonymous I just edited the question to make it more specific. I also added an example output.
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.
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?
