cancel
Showing results for
Did you mean:
New Member

## Multiply a Field by a Filter Range

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
Super User

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.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors