Aggregation of values from a different table based on filters on multiple tables

[Sujit_Nayak]

Hi,

I'm looking for some help to solve a problem I have:

I have a table of Materials and their components and another table with Component Materials and their Prices:
ProductComp:

Material ID	Component Material ID
763	86556
763	64626
763	2692
763	2702
763	2703
763	2713
763	2714
763	2715
763	2720

CompPrice:

Material ID	Price	Start Date	End Date
86556	5.00	01-Oct-2023	31-Dec-2023
86556	5.50	01-Jan-2024
64626	23.43	01-Oct-2023	31-Dec-2023
64626	23.87	01-Jan-2024
2692	1.56	01-Oct-2023	31-Dec-2023
2692	2.20	01-Jan-2024
2702	150.00	01-Oct-2023	31-Dec-2023
2702	156.00	01-Jan-2024
2703	40.25	01-Oct-2023	31-Dec-2023
2703	40.29	01-Jan-2024
2713	0.45	01-Oct-2023	31-Dec-2023
2713	0.56	01-Jan-2024
2714	12.00	01-Oct-2023	31-Dec-2023
2714	13.00	01-Jan-2024
2715	5.00	01-Oct-2023	31-Dec-2023
2715	5.46	01-Jan-2024
2720	1.45	01-Oct-2023	31-Dec-2023
2720	1.48	01-Jan-2024

I then have a table of Inventory. I need to add a column to this table to have the sum of prices of components of the Material ID of the Inventory.

Inventory:

Inventory ID	Material ID	Active Date	SUM of price
100001	763	01-Jan-2024	?

Any help will be greatly appreciated, please.

[Ashish_Mathur]

Hi,

Assuming there will never be repetitions in the Material column of the CompPrice table, create a relatiopnshipe (Many to One and Single) from the ProductComp table to the CompPrice table.  Write a RELATED() function int the ProductComp table to bring over the Price from the PriceComp table.  In your visual, you should now be able to write this simple measure

T = sum(ProductComp[Price])

[Sujit_Nayak]

@Ashish_Mathur You caught me out there :). The CompPrice table indeed has repetitions (I left it out for simplicity, but I guess it is pertinent). The Prices for a Material ID are for a date range - start and end dates. So a material ID does repeat for different prices for different applicable date ranges.

The price to lookup from CompPrice table for an Inventory is based on the condition where a Date field (Active date) from the Inventory table is within the date range for each of its components.
I've updated the table schema to reflect this. 
Also, I'd like to add the sum as a new column to the Inventory table and not a measure in a visual, please.

[Ashish_Mathur]

Hi,

Show the result you are expecting and also justfy why do you want a calculated column formula (why not a measure?)

[PhilipTreacy]

Hi @Sujit_Nayak 

Download example PBIX file

Create the 3 tables and create relationships like so, each table is linked on the Material ID.

You can then display the Inventory table with the 3rd column being the Price from the CompPrice table.  Choose the aggregation Sum for this column.

Regards

Phil

[Sujit_Nayak]

@PhilipTreacy : Thanks for the response. But please see my reply to @Ashish_Mathur . I guess you too assumed the one to many relationship from CompPrice to ProductComp ? Also I missed out mentioning the condition to retrieve the price based on a date creiteria from the Inventory table to match the correct date range from the CompPrice table. I've updated the original post with these details. Apologies for the confusion.

[Anonymous · Accepted solution]

Hi @Sujit_Nayak 

Please try this:

Delete all the relationships between the tables:

Then add a calculated column in the Inventory table:

Column =
	VAR _vtable = FILTER(
		CROSSJOIN(
			'CompPrice',
			'ProductComp'
		),
		'CompPrice'[Material ID] = 'ProductComp'[Component Material ID]
	)
	RETURN
		SUMX(
			FILTER(
				_vtable,
				'ProductComp'[Material ID] = 'Inventory'[Material ID] && 'CompPrice'[Start Date] <= 'Inventory'[Active Date] && 'Inventory'[Active Date] <= 'CompPrice'[End Date]
			),
			[Price]
		)

Given your CompPrice table is missing some End Date data, I haven't get the target result.

Best Regards

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