Average of Change by crop year

[Dellis81]

Hello!

I am needing help on something that is probably obvious.  Within a matrix, the total column - I would like to show the AVG change  of all crop years.   Since 2017 is the first year, there would be no change value displayed - and excluded from the average

File link https://1drv.ms/u/s!AmBVCme14p7xszd7fzk4r4eDkxlp?e=EuJeIx

MeasureName = YOY TEST

PS - what I really want to get to is % Change YOY, but need to get this initial step in place first.

YOYTest = 
Var LastYr = min(ProjectSetup[Crop Year])-1
VAR LastYrValue = CALCULATE([IncomeStatement/Crop Acre],ProjectSetup[Crop Year]=LastYr)
        VAR ThisYrValue = [IncomeStatement/Crop Acre]
        VAR IfZero = OR( ThisYrValue = 0, LastYrValue = 0 )
        vAR AnnualChange = if(IfZero,BLANK(),ThisYrValue-LastYrValue)
        //vAR AnnualChange = ThisYrValue-LastYrValue

Var ValueAvgX = AVERAGEX(
    FILTER(values(ProjectSetup[Crop Year]),ProjectSetup[Crop Year]>min(ProjectSetup[Crop Year])),
    
    //VALUES( 'ProjectSetup'[Crop Year] ),
        Var LastYr = min(ProjectSetup[Crop Year])-1
        VAR LastYrAvgXValue =
            CALCULATE(
        [IncomeStatement/Crop Acre],
                ProjectSetup[Crop Year]=LastYr)
        VAR ThisYrAvgXValue = [IncomeStatement/Crop Acre]
        VAR IfZero = OR( ThisYrValue = 1, LastYrValue = 0 )
        vAR AvgXChange = if(IfZero,BLANK(),ThisYrValue-LastYrValue)
        Return 
        //AvgXChange)
        ThisYrAvgXValue-LastYrAvgXValue)
RETurn
//ValueAvgX
if(HASONEVALUE(ProjectSetup[Crop Year]),AnnualChange,ValueAvgX)

[Ashish_Mathur]

Hi,

This measure works

Measure = if(HASONEVALUE(ProjectSetup[Crop Year]),[YOYTest],AVERAGEX(VALUES(ProjectSetup[Crop Year]),[YOYTest]))

Hope this helps.

[Dellis81 · Accepted solution]

Yes!  Thank you... I knew it would be a simple solution. ugh!   I think both answers ended up doing the same thing.   But if I may ask a followup question?     Is it possible to make this all work within a single measure?   When I am just taking hte average over years - I have a nice one measure solution.   Having introducing "YOY change - that seemed to complicate?

I realize we would be nesting variables, etc inside an AvgX - but trying to keep my models from being cluttered with extra measures.    

The value circled in blue below is an example of single vs Avg calculations within a single measure.
Thank you again!

[Ashish_Mathur]

Hi,

If i try, i probably can but i would not want to do so just to keep the measures short and easy to understand.  If my previous reply helped, please mark it as Answer.

[Dellis81]

Hello - my intention with this measure is to use in conditional formatting.   I have alot of additional pieces worked out - and what you are seeing is just the problematic part.   I hope to insert this larger, single measure inside a KPI trend indicator.  Thanks!

[Ashish_Mathur]

Hi,

Even if you have a chain of measures and the last measure gives you the correct measure, then apply conditioal formatting with the last measure.  You will not face a problem.

[v-luwang-msft]

Hi @Dellis81 ，

Final get is this:

Do the below steps:

Step 1,create new measure like below:

YOYTest1 = 
VAR LastYr =
    MIN ( ProjectSetup[Crop Year] ) - 1 //GET 2016
VAR LastYrValue =
    CALCULATE ( [IncomeStatement/Crop Acre], ProjectSetup[Crop Year] = LastYr )
VAR ThisYrValue = [IncomeStatement/Crop Acre]
VAR IfZero =
    OR ( ThisYrValue = 0, LastYrValue = 0 )
VAR AnnualChange =
    IF ( IfZero, BLANK (), ThisYrValue - LastYrValue )
VAR test1 =
    AVERAGEX ( VALUES ( 'ProjectSetup'[Crop Year] ), AnnualChange )
RETURN
    AnnualChange

Step 2,then base on the measure create a new measure:

Measure = AVERAGEX(VALUES('ProjectSetup'[Crop Year]),[YOYTest1])

Step ,change the base measure YOYTEST to the below:

YOYTest = 
Var LastYr = min(ProjectSetup[Crop Year])-1
VAR LastYrValue = CALCULATE([IncomeStatement/Crop Acre],ProjectSetup[Crop Year]=LastYr)
        VAR ThisYrValue = [IncomeStatement/Crop Acre]
        VAR IfZero = OR( ThisYrValue = 0, LastYrValue = 0 )
        vAR AnnualChange = if(IfZero,BLANK(),ThisYrValue-LastYrValue)
        //vAR AnnualChange = ThisYrValue-LastYrValue 
RETurn
//ValueAvgX
if(HASONEVALUE(ProjectSetup[Crop Year]),AnnualChange,_MeasureTable[Measure])

Wish it is helpful for you!

Best Regards

Lucien

[v-luwang-msft]

Hi @Dellis81 ，

Is what you want to show in the Avg=(37.32-82.94)/2?

Best Regards

Lucien

[Dellis81]

Yes, apologize for lack of clarity.  Thank you!