Search code examples
excelaveragepowerpivotdax

Powerpivot average, only show a range of rows

I have done an average price calculation for apples. See example

In the new column "testar", I only want to show the year 1990 - 1994 (yellow cells), since the other years are not specified in my formula. Formula I used for average calculation was:

=CALCULATE (
AVERAGEX (Datasrc; Datasrc[C_P2] );
DATESBETWEEN(Datasrc[Year];"1990-01-01";"1994-01-01")
)

Any ideas or advice how to do that?

Solution

  • You'll need to test the values of Datasrc[Year] in an IF().

    Here's a sample using a dummy dataset I have.

    Testar = 
IF(
    MAX( DimDate[Year] ) > 2010
        && MAX( DimDate[Year] ) < 2014
    ,[SumAmt]
)

    We're testing MAX() DimDate[Year]. On any given pivot row, only one year is in context, so max is the year on that pivot row.

    The measure you used calculates the average in the context of the 5 years you've defined, overriding whatever the current filter context is.

    Additionally, AVERAGEX() is unnecessary in this situation; you can use AVERAGE( Datasrc[C_P2] ).

    enter image description here