DAX: DISTINCT() as a solution?

[vega805]

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>

 

[powerpivotpro]

You can do this in a measure, or in a calc column in tblCycleRanch.

The measure is something like: SUM(tblProduction[sumofQtyReceived]) / AVERAGE(tblProduction[acres])

The calc column is something like: SUMX(RELATEDTABLE(tblProduction), tlbProduction[sumofQtyReceived]) / AVERAGEXRELATEDTABLE(acres), tlbProduction[acres])

Assuming I understood the question.

 

[vega805]

Rob, its somewhat of an honor to have you reply to my thread Your site, Ferrari and Russo have been a large part of my power pivot ramp up as an analyst and to take the tool deeper. Your solution sounds right, Ill require the calculated column versus measure in that Ill need the results in the pivot table and inline to the ranch column or row. I will attempt, however lately in this model and others, I'm having an issue using RELATE/referring to fields in related tables in a calculated column and don't quite understand why - I get an error message that the relationship doesn't exist or needs to be created when it does exist. I'm not sure if this is the data type but since I have to forge ahead I wind up trying a workaround. Anyway, I will try this and see what happens. Thanks and love your movie quotes.

 

[powerpivotpro]

Relationships have a direction. If you try to use one in the wrong direction, it will tell you a relationship does not exist. RELATED() only works to fetch values from the Lookup table into the data table. If you try to use RELATED() in the Lookup table, to fetch values from the data table, boom - "relationship needed" error.

Note that my calc column uses SUMX(RELATEDTABLE()) - RELATEDTABLE() can be thought of as the "opposite" of RELATED(), as it works the other direction, but you always need an aggregation function wrapped around it.

 

[vega805]

Per the original purpose of my post, I would have to go the measure route as I would need a distinct measure, yield per acre, for each ranch. There are 20 to report for. I don’t see how I could relate a measure to a ranch dimension. I have dimensions such as region, hoops, organic. When I filter via slicer my pivot changes, but not the measure, I’d have to include/exclude manually.
I need to feature yields in a few dimensions, by region, by variety and by ranch – and per acre at that. Since I need to move forward with reliable information Id rather query in Access and import a couple tables to my data model.

Relationships have a direction. If you try to use one in the wrong direction, it will tell you a relationship does not exist. RELATED() only works to fetch values from the Lookup table into the data table. If you try to use RELATED() in the Lookup table, to fetch values from the data table, boom - "relationship needed" error.

Note that my calc column uses SUMX(RELATEDTABLE()) - RELATEDTABLE() can be thought of as the "opposite" of RELATED(), as it works the other direction, but you always need an aggregation function wrapped around it.

This is very helpful and resolved some ongoing issues. I did not realize this nor came across it in all my reading. Thank you.

 

[vega805]

On the topic of related tables, one more inquiry if you would… Incidentally, Im featuring production over time. The measure of time in the production table is pool week, the week of the year prefixed with the year (this week would be 1333). Instead of having pool week on the x axis or in power view scatter chart, I’d like a date. I can’t figure out how to relate pool week to date, intuitively, Id like to include weekend date in the pool week table by a calculated column. I have the following date dimension tables.
poolWeekDate
dateID
poolWeekID
date
weekStart
weekEnd

Pool Week
poolWeekID
poolWeek