Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
shipper423
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: 

shipper423_1-1657812396892.png

 

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 

 

LaneWeekProductionExpected Production per WeekOver/UnderVendorActual Expected Weekly ProductionActual Over/Under
Chicago - Tokyo30360.173076923-35.82692308ABC 0.346153846-35.65384615
LA - Paris30511.307692308-49.69230769ABC 2.615384615-48.38461538
LA - Hamburg30262.576923077-23.42307692ABC 5.153846154-20.84615385
Savannah - Gebze30562.211538462-53.78846154ABC 4.423076923-51.57692308
Seattle - Shanghai3042.326923077-1.673076923ABC 4.6538461540.653846154
Chicago - Tokyo3116.90.173076923-16.72692308ABC 0.346153846-16.55384615
LA - Paris31111.307692308-9.692307692ABC 2.615384615-8.384615385
LA - Hamburg315.12.576923077-2.523076923ABC 5.1538461540.053846154
Savannah - Gebze3122.2115384620.211538462ABC 4.4230769232.423076923
Seattle - Shanghai3122.3269230770.326923077ABC 4.653846154

2.653846154

 

1 REPLY 1
lbendlin
Super User
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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors