I'm new with DAX and I need some help with a measure.
I want to group data by month, product, group and store, and summarize quantity and goals.
These are a sample of my tables:
Stores table
Targets table
| Month | Store | Product | Goal |
| feb 21 | 1 | A | 5 |
| feb 21 | 1 | B | 7 |
| feb 21 | 2 | A | 8 |
| feb 21 | 2 | B | 7 |
| feb 21 | 3 | A | 6 |
| feb 21 | 3 | B | 10 |
Sales table
| Date | Store | Product | Quantity |
| feb 1, 21 | 1 | A | 2 |
| feb 2, 21 | 1 | A | 5 |
| feb 5, 21 | 1 | B | 3 |
| feb 5, 21 | 2 | A | 1 |
I need something like this, filtered by month.
Sum all goals even if there are no sales for some stores yet.
Feb 21
| Product | Group | Store | total Quantity | total Goal |
| A | | 6 | 8 | 19 |
| | XX | 3 | 8 | 13 |
| | | 1 | 7 | 5 |
| | | 2 | 1 | 8 |
| | YY | 3 | | 6 |
| | | 3 | | 6 |
I don't have problems if there are some sales for the store, but when a store doesn't have any rows in the sales table, the total goal colum isn't correct.
This is the result I have, the total goal of product A in feb 21 (13) is wrong.
Feb 21
| Product | Group | Store | total Quantity | total Goal |
| A | | 6 | 8 | 13 |
| | XX | 3 | 8 | 13 |
| | | 1 | 7 | 5 |
| | | 2 | 1 | 8 |
| | YY | 3 | | __ |
| | | 3 | | __ |
Can anyone help me with this issue?
Thanks
