cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
siros92
Frequent Visitor

help needed with growth estimation

Cheers everyone!

As my DAX skills is far from superb i find myself in the need of some help from the community.
I'm currently creating growth estimations for Atlantic salmon based on public data. I have all the info i need it's just a matter of formulation...
My data is reported week by week, and as temperature is an important factor which is varying throughout the year i want the equation to calculate the growth this week and add it to the growth last week.
Generations goes over several years, therefore i'm using a globalweek approach to move away from year and date issues (Instead of restarting at the start of each year global week countiniues i.e week 52,53,54).
In my dataset there is a high amount of sites, so i need to be able to apply filters. I would do this based on a column called [site number], which holds a uniqe number for each site.

Every time there is a new generation of Salmon the column [weeks since sea-transfer] restarts at 1 and then increases +1 for every week.

the equation for growth this for this would be:
weight end = (weight start ^1/3 + Growthfactor * Temperature * 7)/1000) ^1/3)
- The growthfactor is confidential so I cannot share it.

Any suggestions for a formula? Here is a picture of the data at interest.

siros92_0-1593008922744.png

 


All help welcome!
Cheers,

6 REPLIES 6
lbendlin
Super User III
Super User III

that formula looks fishy.  sorry, codn't resist. SORRY AGAIN!

 

weight end = (weight start ^1/3 + Growthfactor * Temperature * 7)/1000) ^1/3)

 

as written (and ignoring the last bracket) would be:

 

weight end =

var ws = max[weight start]

var temp=max[Temperature]

var factor = DIVIDE(1,3)  -- yes, overkill, i know

return POWER(DIVIDE(POWER(ws, factor) + [Growthfactor]*temp*7),1000),factor)

siros92
Frequent Visitor

Haha, i need a glass of water with that one! 

 

I did some small ammendments to your suggestion to accomodate the need for filtering. image.png

I also did a excel withdrawal of the result. However, as you can see it didn't yield exactly the result i was looking for.. image.png

So, in essence what i need the [est. size] column to do is to calculate the weight increase of fish at a specific site every week and add it to last week. the starting weight can be found in the column [start size]. This column only holds values when [weeks since sea transfer] = 1. When the [weeks since sea transfer] = 1 a new generation of fish is put to sea and a new cycle is started. Therefore, the column needs to restart everytime [weeks since sea transfer]=1 (which you can see happening in week 25 above).

As there is about 1000 different sites in my dataset i need to be able to filter by [site number].

 

Hope that clarifies,

Any ideas?

Cheers,

what is the result you are looking for? Or can you not tell because that would expose the growth factor?

siros92
Frequent Visitor

No worries, I can understand it's somewhat hard to understand what to get to 😄

i'll just do as I did in the numbers above and use a random number as the growth factor. 

 

So, whenever [weeks since sea transfer] = 1 the rolling calculation needs to restart. This is when a new generation of fish is put to sea. the value should be 0,115 when [weeks since sea transfer] = 1, this is the starting size of the fish. You should be able to see this clearly in the datadraw i posted earlier. 

From there, every week the collumn needs to calulate the result from this equation, and add it to the previous value. 

((((previoous week weigth^(1/3)+2.0*('Lakselus per fisk'[seatemperature]*7))/1000))^1/3). 
As there is about a tousand sites in my sheet I also need to be able to filter by [sitenumber] 

 

so with a constant sea temperature of 7 degrees (to simplify) it will be:
 (((0,115^(1/3)+2.0*(7*7))/1000))^1/3 =0,0328 +0,115 =0,1478

(((0,1478^(1/3)+2.0*(7*7))/1000))^1/3 =0,0328 + 0,1478 = 0,1806

(((0,1806^(1/3)+2.0*(7*7))/1000))^1/3 =0,0328 +0,1806 = 0,213 

And so on.. untill [weeks since sea transfer] = 1

 

Thanks for beeing patient!

Cheers,

 

 

The question here is also - is that a measure (dependent on other filters set by the user) or a calculated column (only computed once)?

 

In any case the formula will be something like

 

[starting weight] * PRODUCTX(...)

 

I need to come back to the original formula

 

weight end = ((weight start ^1/3 + Growthfactor * Temperature * 7)/1000) ^1/3

 

If that is correct (I changed some brackets) then it can be written as

 

weight end = 0.1 * (weight start ^1/3 + Growthfactor * Temperature * 7)^1/3

 

Right?

 

 

 

 

 

siros92
Frequent Visitor

Bump, 

 

Any other suggestions for solving this? 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors