Average with IF statement

[Anonymous]

Hi,

I have a measure to calculate an average of values by year. Is it possible include a condition in this formula so that the very first non-zero yearly value in my data is divided by 12 (months) to get an accurate yearly average.

Thanks in advance. 

[Nathaniel_C]

Hi @Anonymous ,

Would you give us a little more detail? Is there a condition that could be used to signify the first non zero value? Could I say "IF the row before this is zero then divide by 12?
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Anonymous]

@Nathaniel_C ,

The data is for oil and gas production and the way our database is set up means that all fields start producing from the year 1960 and extend to 2060, with many zero's at the beginning and end for many of the fields.

As for your question - there are also zero values throughout the data so it is only the very first non-zero value that I would like to divide by 12. All other values will be averaged by the number of months of production, which is working as expected. 

So rather than "IF the row before this is zero then divide by 12" I need something along the lines of "IF the first value <> 0 then divide by 12, otherwise average by the number of production months".

Thanks

[Anonymous]

@Nathaniel_C 

So I have figured that I need a flagging calculated column to mark the First Occurence of the non-zero value. 

How do I now write the measure to say something along the lines of "IF First Occurance = Yes then divide by 12, else average"

Thanks