Calculate sum of amount based on items included in each category

[djemy1975]

Dear colleagues,

I have 2 tables:

How to calculate the sum of stock amount in "stock" table based on "PCC CODE" in "Movement" table when items match in both tables and how shall I connect these 2 tables? so that I can filter data with PCC CODE and month afterwards.

Best regards,

 

[I_cant_read]

You could try and merge the two tables using the four fields. (Item code/description/purchasing family/month) If it is a small data set.
1.) Load Stock as Query 1
2.) Load Movement as Query 2
3.) Merge both using left outer.
4.) Group By "Advanced" > Add grouping PCC Code > etc

Or you could use create a relation between the two tables using Item code (one is to many)
Then create a measure using SUM and directly create a pivot table using PCC code as top in the hierarchy followed by the subsequent fields.

SUM function (DAX) - DAX

Learn more about: SUM

docs.microsoft.com

 

[djemy1975]

You could try and merge the two tables using the four fields. (Item code/description/purchasing family/month) If it is a small data set.
1.) Load Stock as Query 1
2.) Load Movement as Query 2
3.) Merge both using left outer.
4.) Group By "Advanced" > Add grouping PCC Code > etc

Or you could use create a relation between the two tables using Item code (one is to many)
Then create a measure using SUM and directly create a pivot table using PCC code as top in the hierarchy followed by the subsequent fields.

SUM function (DAX) - DAX

Learn more about: SUM

docs.microsoft.com

Thank you for your reply.
In fact I have opted for the second solution as the data is very big .I used the Item code as a relation many to many and it is working on both directions when:
1-Using PCC code as slicer from "Movement" to filter the matrix
2-Using the purchasing segment from "stock" table as slicer to filter matrix
Best regards,

 

[djemy1975]

You could try and merge the two tables using the four fields. (Item code/description/purchasing family/month) If it is a small data set.
1.) Load Stock as Query 1
2.) Load Movement as Query 2
3.) Merge both using left outer.
4.) Group By "Advanced" > Add grouping PCC Code > etc

Or you could use create a relation between the two tables using Item code (one is to many)
Then create a measure using SUM and directly create a pivot table using PCC code as top in the hierarchy followed by the subsequent fields.

SUM function (DAX) - DAX

Learn more about: SUM

docs.microsoft.com

As I said ,I used the stock item code as a relation between stock table and movement ,but the problem is when one item code from "stock" table is not found in the "movement" table the matrix shows nothing when purchasing segment is filtered as you can see here: I am using a measure to display records in a matrix as follows: Amount = CALCULATE(sum(Stock[Amt Month])
Shall I modify this formula or what?

Regards,

 

[I_cant_read]

Hi there. I was thinking something like this for the matrix table. Maybe add the purchasing segment on the encircled part?
Not exactly sure how to proceed beyond this.

Cheers.

 

[djemy1975]

Hi there. I was thinking something like this for the matrix table. Maybe add the purchasing segment on the encircled part?
Not exactly sure how to proceed beyond this.
View attachment 34780

Cheers.

Thank you for your interest . The problem is that there are item codes that are found in "Movement" table and not found in "Stock" table and vice versa, that is why the filtering with them gives different result. The question is :is there any possibility to make a "mapping table" that adds the new item codes from both tables whenever the two tables are updated with new item codes each month Like that:

NB: each "PCC code category" and "purchasing segment" category contains different group of item codes

As I can't find "purchasing segment" categorization anymore from February onward, I want to keep it besides "PCC code" and get the mapping from previous two months (December and January).

Hope my idea is clear now.

Best regards,

 

[I_cant_read]

Hi there.

Have you tried to append the data before loading...

1.) Load "Stocks" as connection
2.) Load "Movement" as connection
3.) Append the two tables and only use the columns you need.
4.) Then apply the measures

Using your illustration. It seems like it is inventory movement.

Try playing around merge / append options in Power Query. That is what i am thinking about when viewing the illustration above.

 

[djemy1975]

I will try to apply this method.Thanks