Forum Discussion

jaymetbar's avatar
jaymetbar
Frequent Visitor
6 years ago
Solved

Need help modifying a production number based on other data points

New to Power BI and I am trying to generate reports based on our production cells. I am ultimately trying to piece together something more complicated than this, but I am stuck at this current point of the problem.

 

Overview: We produce lbs. of product out of different vessels. We have the vessels that we harvested noted in a day. We need to pull a piece of production out of each vessel to sample the quality. We take all those samples and assign their production lbs. to an arbitrary Vessel #1 for the day. 

 

Please see the data below:

Production DateVessel NumberProduction
1/1/19991111654
1/1/19991210976
1/1/19991312673
1/1/19991410769
1/1/19991510628
1/1/19991612563
1/1/19991710290
1/1/19991810031
1/1/19991912518
1/1/19992012246
1/1/199912645
1/2/19992112310
1/2/19992211833
1/2/19992310139
1/2/19992411800
1/2/19992511702
1/2/19992610470
1/2/19992712182
1/2/19992810665
1/2/19992910880
1/2/19993010037
1/2/199912758

 

What I'm trying to accomplish:


I need to create a new column that runs along side the "Production" column which calculates the true vessel production based on the sample weight being added back to the vessel production.

 

The formula should be something like:

True Vessel Production = (Vessel Production + (Vessel #1 Production Weight/Count of Vessels Harvested in a day  <> 1)

 

For example, the True Production for Vessel #11 on 1/1/1999 should equal to:

 

TP = (11654+(2645/10)) = 11919

 

I hope this request makes sense. Please let me know if you have any questions and I'd be happy to clarify.

 

I'm hoping to use this number to calculate a vessel efficiency since I can calculate what the theoretical production should be. I may need help averaging a value between the dates that a certain vessel was harvested, but that's a question for another time.

 

I appreciate the help!

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Perhaps:

     

    Column = 
    VAR __Vessel1Value = MAXX(FILTER('Table',[Vessel Number] = 1 && [Production Date] = EARLIER('Table'[Production Date])),[Production])
    VAR __Count = COUNTROWS(FILTER('Table',[Vessel Number] <> 1 && [Production Date] = EARLIER('Table'[Production Date])))
RETURN
    [Production] - __Vessel1Value / __Count

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Perhaps:

     

    Column = 
    VAR __Vessel1Value = MAXX(FILTER('Table',[Vessel Number] = 1 && [Production Date] = EARLIER('Table'[Production Date])),[Production])
    VAR __Count = COUNTROWS(FILTER('Table',[Vessel Number] <> 1 && [Production Date] = EARLIER('Table'[Production Date])))
RETURN
    [Production] - __Vessel1Value / __Count