Combining Tables in PowerPivot while applying math (Windows 10, Office 2016)

[tompatties]

Hi folks, looking to use PowerPivot in Excel 2016 (Windows 10) generate a table which combines two existing tables and apply math to the units column. Any idea how to get this done? Thanks in advance!

INPUTS:
Unit Sales:

[TABLE="width: 430"]
<tbody>[TR]
[TD]Product
[/TD]
[TD]Product Group
[/TD]
[TD]Month
[/TD]
[TD]Units
[/TD]
[/TR]
[TR]
[TD]Small Widget Red
[/TD]
[TD]SW
[/TD]
[TD]June
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]Small Widget Blue
[/TD]
[TD]SW
[/TD]
[TD]July
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]Large Widget Blue
[/TD]
[TD]LW
[/TD]
[TD]June
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]Medium Widget
[/TD]
[TD]MW
[/TD]
[TD]July
[/TD]
[TD]40
[/TD]
[/TR]
</tbody>[/TABLE]

Accessory attach rates:

Accessory	Product Group	Attach Rate
Universal Acc.	All	10%
SW Acc. A	SW	50%
SW Acc. B	SW	100%
LW Acc. B	LW	100%

<tbody>
</tbody>

ACTION: Apply Accessory Attach Rates to the Device Pipeline Units by Product Group

OUTPUT: Accessory Unit Sales

Accessory	Fiscal Month	Units
Universal Acc.	June	2.5
Universal Acc.	July	6
SW Acc. A	June	0.5
SW Acc. A	July	1
SW Acc. B	June	10
SW Acc. B	July	20
LW Acc. B	June	15

<tbody>
</tbody>

 

[tompatties]

Update: I loaded the following code into DAX Studio and got the table I am looking for. When I try to nest in a SUMX formula to get a sum of [pipelineaccessoryunits], I get an error saying it cannot identify the table which contains [pipelineaccessoryunits]. I get that the column is located in the in-memory table I created with addcolumns() and generate(), but how do I reference it?

ADDCOLUMNS(
Filter(
generate(
SUMMARIZE(
Pipeline,
Product[Product Group],
Calendar[Fiscal Month],
"PipelineDeviceUnits",
sum(Pipeline[Total])
),
SUMMARIZE(
Product,
Product[product type],
Product[Product Family],
Product[ASP],
Product[Attach Rate]
)
),
CALENDAR[fiscal Month]<>blank()
&&Product[Product Type]="Accessory"
),
"PipelineAccessoryUnits",
[attach rate]*[pipelinedeviceunits]
)

 

[scottsen]

Any relationships going on here? Somehow it feels like I should see a [Units] * RELATED([Attach Rate]) or ... [Attach Rate] * SUMX(RELATEDTABLE(Sales), Sales[Units]) ... or... something?