Forum Discussion

AlejandroPCar's avatar
AlejandroPCar
Icon for Helper IV rankHelper IV
9 years ago
Solved

Average Annual Percent Change

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!

 

 

 

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    9 years ago

    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:

     

10 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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