Powerpivot average, only show a range of rows

Question

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?

score 3 · Accepted Answer · answered Jan 15 '16 at 21:58

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] ).

score 0 · Answer 2 · answered Jan 15 '16 at 22:40

Almost, thank you for your valuable input and put me in the right direction!!!, since my format of the year table is different and I want to have the average price listed I did a little different. I simply combined your formula with my average formula and got the result I wanted. See final result here

Code used:

=IF(
    MAX( Datasrc[Year2] ) >= 1990
        && MAX( Datasrc[Year2] ) <= 1994
    ;
CALCULATE (
AVERAGEX (Datasrc; Datasrc[C_P2] );
DATESBETWEEN(Datasrc[Year];"1990-01-01";"1994-01-01")
)
)

The first part of the formula, takes out only the years I'm interested in. The second part of the formula [CALCULATE(AVERAGEX....] calculates the average of the years I'm interested of.

Powerpivot average, only show a range of rows

2 Answers2