I have a powerpivot data model producting expense and production data. The data sources are from an Access database.
regarding creating calculated fields, Im stuck in the following scenario.
2 tables:
tblCycleRanch</SPAN>
ranchNumber (unique identifier) PK -> tblProduction.ranchNumber
ranchName
commodity
region
hoops
organic</SPAN>
tblProduction (aggregated exported table)</SPAN>
ranchNumber
varietyName
acres
sumOfQtyReceived
poolWeek
yieldAcre
tblProduction provides me with a weekly yield/acre by variety calculated column (sumOfQtyReceived/acres), which is why I queried as such in Access. This is the most granular I wish to get. </SPAN>
However, now im interested going the other way, calculate from a macro perspective - the yield of the ENTIRE ranch for all pool weeks, so in essence I’m trying to create a calculated field for each ranch (SUM(sumOfQtyReceived)/SUM(acres). I can aggregate total production but I am not able to aggregate total acres per ranch since they are repetitive in tblProduction. Im wondering if I could use a DISTINCT() clause to isolate those ranches acres. </SPAN>
Any ideas? </SPAN>
Second to this and also a potential solution, if I were to have a ranchVariety table, that contained a unique ranchVarietyId value for each ranch and variety (joined between tblCycleRanch and tblProduction), how would I carry over the ranchVarrietyId into a calculated column of tblProduction? IF(CR.ranchID=P.ranchID && CR.varietyName=P.varietyName, tbl.ranchVariety.anchVarietyId )?</SPAN>
regarding creating calculated fields, Im stuck in the following scenario.
2 tables:
tblCycleRanch</SPAN>
ranchNumber (unique identifier) PK -> tblProduction.ranchNumber
ranchName
commodity
region
hoops
organic</SPAN>
tblProduction (aggregated exported table)</SPAN>
ranchNumber
varietyName
acres
sumOfQtyReceived
poolWeek
yieldAcre
tblProduction provides me with a weekly yield/acre by variety calculated column (sumOfQtyReceived/acres), which is why I queried as such in Access. This is the most granular I wish to get. </SPAN>
However, now im interested going the other way, calculate from a macro perspective - the yield of the ENTIRE ranch for all pool weeks, so in essence I’m trying to create a calculated field for each ranch (SUM(sumOfQtyReceived)/SUM(acres). I can aggregate total production but I am not able to aggregate total acres per ranch since they are repetitive in tblProduction. Im wondering if I could use a DISTINCT() clause to isolate those ranches acres. </SPAN>
Any ideas? </SPAN>
Second to this and also a potential solution, if I were to have a ranchVariety table, that contained a unique ranchVarietyId value for each ranch and variety (joined between tblCycleRanch and tblProduction), how would I carry over the ranchVarrietyId into a calculated column of tblProduction? IF(CR.ranchID=P.ranchID && CR.varietyName=P.varietyName, tbl.ranchVariety.anchVarietyId )?</SPAN>