Multiply a Field by a Filter Range

[shipper423]

Hi!

I am creating a forecast to determine if vendors are meeting or not meeting expected production (over or under) for their locations (lanes). My question is: how can I calculate the amount the vendors are over/under production within a time range (week number).

My expected production stays the same weekly. If I filter my slicer to look at multiple weeks' productions (say I filter for Week 28 - Week 32), I am comparing the total production from 5 weeks to the expected production of 1 week, when really, it should be the total production for 5 weeks compared to the expected production for 5 weeks. 

Therefore, how can I multiply my expected production against the distinct count of my filtered weeks?

I have 2 tables I'm using: 

1. Table 1: vendor, location, & expected production
2. Table 2: actual production, over/under amount, week number

My only join is a many-to-many, active relationship between table 1 & table 2 for the location.

Here is the current powerbi table and process I have: 

So again, I'm looking to have that Expected Production amount * filtered week range (7) = Over/under amount

As an excel file, this process (for 1 vendor) would look something like: 

• With the Actual Expected Weekly Production as: 
  • Actual Expected Weekly Production = Expected Production per Week * 2
  • Actual Over/Under = Actual Weekly Production - sum(C2 + C7) 
    • with C2 and C7 being the production of week 30 and 31 for Chicago - Tokyo 

Lane	Week	Production	Expected Production per Week	Over/Under	Vendor	Actual Expected Weekly Production	Actual Over/Under
Chicago - Tokyo	30	36	0.173076923	-35.82692308	ABC		0.346153846	-35.65384615
LA - Paris	30	51	1.307692308	-49.69230769	ABC		2.615384615	-48.38461538
LA - Hamburg	30	26	2.576923077	-23.42307692	ABC		5.153846154	-20.84615385
Savannah - Gebze	30	56	2.211538462	-53.78846154	ABC		4.423076923	-51.57692308
Seattle - Shanghai	30	4	2.326923077	-1.673076923	ABC		4.653846154	0.653846154
Chicago - Tokyo	31	16.9	0.173076923	-16.72692308	ABC		0.346153846	-16.55384615
LA - Paris	31	11	1.307692308	-9.692307692	ABC		2.615384615	-8.384615385
LA - Hamburg	31	5.1	2.576923077	-2.523076923	ABC		5.153846154	0.053846154
Savannah - Gebze	31	2	2.211538462	0.211538462	ABC		4.423076923	2.423076923
Seattle - Shanghai	31	2	2.326923077	0.326923077	ABC		4.653846154	2.653846154

[lbendlin]

Please provide sanitized sample data that fully covers your issue ( as separate tables)

Please show the expected outcome based on the sample data you provided.