Average Annual Percent Change

[AlejandroPCar]

Hi!

I need some help here. How can I do a measure of Average Annual Percent Change? I mean, I have a table like this one:

For example, the Average Annual Percent Change for the first row (1 INVERSIONES RAM LTDA) will be =( 0,0% + -77,99% - 47,56%) /  3 (3 years selected) = -41,85%. And then, if I need to select more years the measure also calculate it.

Thanks a lot!

[v-jiascu-msft]

Hi @AlejandroPCar,

@Ashish_Mathur's solution is perfect. If you have any question, please post here. 

Best Regards!

Dale

[v-jiascu-msft]

Hi @AlejandroPCar,

What's your the structure of your model? What's the formula of Annual Percent Change? Here is a sample. You can reference.

One product table 'Product', one date table 'Date', one sales data table 'Sales'.

The Annual Percent Change here is: 

Measure 16 =
VAR QuantityLastYear =
    CALCULATE ( SUM ( Sales[Quantity] ), PREVIOUSYEAR ( 'Date'[Date] ) )
VAR QuantityThisYear =
    SUM ( Sales[Quantity] )
RETURN
    DIVIDE ( QuantityThisYear - QuantityLastYear, QuantityLastYear, 0 )

Then the Average Annual Percent Change is:

Measure 17 =
AVERAGEX (
    SUMMARIZE (
        'Sales',
        'Product'[Color],
        'Date'[Date].[Year],
        "Percentage",
        VAR QuantityThisYear =
            SUM ( Sales[Quantity] )
        VAR QuantityLastYear =
            CALCULATE (
                SUM ( Sales[Quantity] ),
                PREVIOUSYEAR ( 'Date'[Date] ),
                'Product'[Color] = EARLIER ( 'Product'[Color] ),
                ALL ( Sales )
            )
        RETURN
            DIVIDE ( QuantityThisYear - QuantityLastYear, QuantityLastYear, 0 )
    ),
    [Percentage]
)

A little complicated. If you provide a sample, I can help you. The PBIX file is great. A sample in text mode is also OK.

Best Regards!

Dale

[AlejandroPCar]

Hi @v-jiascu-msft

One question, why the total of your example does not work like average result in the selected years? Here is my pbix file: https://1drv.ms/u/s!AtTnrgPUQzQCjCb_Ut1ydMbZyJbk Thank you a lot for help me.

[Ashish_Mathur]

Hi,

So in the second Table, you would like to see average annual percentage in the Total column.  Am i correct?

[AlejandroPCar]

Not necessarily but could be helpful thanks

[Ashish_Mathur]

Then what exact result are you expecting?

[AlejandroPCar]

Just the measure that calculates the average annual percent change in the years I select. And if it is possible the years filter also modifies the table showing just the single years and its annual change. SO IF i select 2007, 2008, and 2009 for example tus tacos shows me only these years with its annual changes and also the AAPC maybe in the same table or in other site.

[Ashish_Mathur · Accepted solution]

Hi,

This is what i modified your "Variación % Anual ER" measure to

= if(HASONEVALUE(TiempoA[año]),DIVIDE([Variación Anual ER], [Estado Resultados I - 1], 0),AVERAGEX(VALUES(TiempoA[año]),DIVIDE([Variación Anual ER], [Estado Resultados I - 1], BLANK())))

See the Total column in the image below:

[AlejandroPCar]

Thank you very much , it works very well.

[Ashish_Mathur]

You are welcome.