How to do the equivalant of a SUMPRODUCT with VLOOKUP in DAX

[JollyRoger01]

TLDR; skip to the image below

I am trying to write a calculated column that multiplies one value ([Count]) from the current table against another ([Weighting]) from another table where the column [Resource] is the same AND THEN add them up for the rows where [Resource] = "All Resources" IF the columns [Date] and [System] are the same (using the EARLIER function to reference other rows than the current one within the same table).

That's hard to picture so I've added a simple example of what I am trying to do below, with the main goal of the calculation in the yellow cells.

So far I have been able to add up the [COUNT] values from the current table if the [SYSTEM] and [DATE] values are the same as the current row, using the EARLIER function.

=
IF
	(
	[Resource] = "All Resources",
	CALCULATE
		(
		SUM
			(
			[Count]
			),
		FILTER
			(
			Table_Historical_Baselined,
			Table_Historical_Baselined[System] = EARLIER
				(
				Table_Historical_Baselined[System]
				)
			&&
			Table_Historical_Baselined[Date] = EARLIER
				(
				Table_Historical_Baselined[Date]
				)
			)
		),
	[Count]
	)

However I don't know how to multiply these values against their weightings beforehand. I figured this would act similar to SUMPRODUCT with a VLOOKUP/INDEX & MATCH.

I am doing this in Excel but I epect the Power BI DAX should be the same. Below is a copy of the workbook. I would really appreciate any help with this. Please note though if you do provide answer, I am very new to DAX and might struggle to understand it if the reference names are not the same as in the workbook.

Example 

[GoCloudToday · Accepted solution]

Hey,

Taking a look at your workbook, a combination of CALCULATE(SUMX()) should solve your issue.

The core idea is that we need to calculate the product for each row, which will obviously not return a value for the row where we have "All Resources".
Later we sum this up per System, which will result in the sumproducts you are looking for and then simply switch between what values we display for All Resources and the rest.

Below is the formula that solves you issue in my opinion:

=
IF (
Table_Historical_Baselined[Resource] = "All Resources",
CALCULATE (
SUMX (
Table_Historical_Baselined,
[Count]
* LOOKUPVALUE (
XREF_Weighting[Weighting],
XREF_Weighting[Resource], Table_Historical_Baselined[Resource]
)
),
ALLEXCEPT ( Table_Historical_Baselined, Table_Historical_Baselined[System], Table_Historical_Baselined[Date] )
),
Table_Historical_Baselined[Count]
)