Jump to content

Creating Custom Expression for individual averages...

Greg App

Recommended Posts


I am trying to create a custom expression (I believe it would involve an "OVER" function) for a file I am working on.I have a table with 4 different AFE job names: Apple, Banana, Carrot and Duck. These job names are assigned to two different item IDs (T and IT).Each of these 4 different AFE Job names have an assigned Length.

I have calculated the # per Item ID for a particular AFE # (Sum([Amount]) OVER ([Job ID - New AFE],[item ID])

I have also calculated the $ per Item ID for a particular AFE# per Foot ([$ per Item ID for a particular AFE#] / [Length (ft)]

However, I need a way to find the the average of the differentvalues associated with T, as well as with I. However, not ALL values...just different values.

For example, if several listed values associated with T are 5,5,5,4,4,3,3,2 and 1, I don't want to find the average of ALL these numbers. Just the average of 5,4,3,2 and 1, which is 3.

I have attached file to this question for clarification. Pleaes let me know if I need to offer any more clarification.

Link to comment
Share on other sites

Sorry, that was a stupid question on my part, I knew that.


Perhaps it would be easier if I simply stated what I am trying to accomplish.  


I am trying to find the average $/ft for a given categry (Tangible and Intangible), with this $ value being able to change depending on filters and/or drill downs. 


The graph on the bottom shows I am trying to accomplish this...however, after checking them by hand, I see they are off (I didn't take order of operations into account...oops).


Basically, I would need the sum of all "Amounts" for a particualar "Item ID" divided by the sum of all unique "Length" values for a particular "Item ID".


My graph works when I have a single "Job Name-AFE" category selected, but it then falls apart when I have multiple selected (due to order of operations)


This would have to be a dynamic value that would change depending on filters 


(Column names have been placed in Quotations)


Any help would be greatly appreciated.









Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...