Finding the sum and average of aggregated measures?

[Russ Skinner]

Following is an example of two sample measures I'm trying to compile to extract the total and average of aggregated measures.

My data table (linked to powerpivot), is related to s standard calendar table in powerpivot 'Time', relationship on the date field.

My goal is to aggregate 1st and 4th quarter (previous year), as highlighted below, measure 'Winter total' and then to sum and find the average of the figures in the 'Winter Total' column of the powerpivot.

Data table:

Resulting powerpivot table:

Measures as follows:

Quarter 1 =CALCULATE(SUM(Commodity[Total]),Time[Quarter]=1)

Quarter 4 =CALCULATE(SUM(Commodity[Total]),Time[Quarter]=4)

Quarter 4 prev year =CALCULATE([Quarter 4],DATEADD(Time[Date],-1,year))

Winter total =[Quarter 1]+[Q4 Prev Year]

and this is where I run into problems and cannot get the figures to add up:

winter sum total =CALCULATE(SUMX(Commodity,Commodity[Winter Total]), ALL(Time[Year]))

(this measure does not work. Looking for this to equal £1050, the sum of the figures in the winter total column)

winter sum average =CALCULATE(AVERAGEX(Commodity,Commodity[Winter Total]), ALL(Time[Year]))

(this measure does not work. Looking for this to equal £175, the average of the figures in the winter total column)

Any help/ideas greatly appreciated?

Thanks,

Russ

 

[MD610]

The Winter Sum Total doesn't need the SUMX(), it just requires you to remove the filter on Years coming from the pivot.

This should work:
Winter Sum Total:=CALCULATE([Winter Total], ALL(Time))

The Winter Sum Average does require the AVERAGEX() so you can change the granularity of your calculation to work on Years.

This should do the trick:

Winter Sum Average:=CALCULATE(AVERAGEX(VALUES(Time[Year]), [Winter Total]), ALL(Time))

 

[Russ Skinner]

Many thanks Mike. Have just tested the measures you suggest and they work perfectly.

I should have worked out the 'Sum Total', but would have struggled with the Sum Average.

Thanks again,

Russ