AVERAGEX – The 5-point palm, exploding fxn technique revisited

I recently had a problem that needed The 5-point palm, exploding fxn technique as described by Rob in his PowerPivotPro blog post.  I have used SUMX and COUNTX with success a few times before but this time I had a hard time getting my head around my current problem.

While talking with Rob about it I realized I was thinking way to difficult. In this blog post I will try to make more sense of the X functions and give you a little help in building you own X function.

First let’s set a scenario. I want to compare the salesamount of eachyear to the average salesamount over the years. Sound easy huh ..

We start by creating a pivottable based on Contoso with CalendarYear on Y and the sum of salesamount as measure

Next we create a measure to get the average using =AVERAGE(FactSales[SalesAmount])

hmm what happens here?  The description of the function Average is: “Returns the average (arithmetic mean) of all the numbers in a column.”  So what is the function will do is use the lowest grain of data and determine the average of these values, in our case this means he uses all the sales amount values from the FactSales table that are in the current context (year) to calculate the average.

So how do we calculate the average over each year? In Excel this is easy:

And in PowerPivot this is essentially the same, we can use the AVERAGEX function to calculate average over a specific table. The description from MSDN is:

Calculates the average (arithmetic mean) of a set of expressions evaluated over a table.

This means we can change the scope of the average, the normal average automatically will take the lowest grain, but now we can supply our own table it has t o calculate the average. We want the function to do the same as the Excel calculation above. Our function should do average over:

2007€ 4.561.940.9552008€ 4.111.233.5352009€ 3.740.483.119

And this is exactly how the AVERAGEX should be used:

=AVERAGEX(all(DimDate[CalendarYear]), FactSales[Sum of SalesAmount])

The all(DimDate[CalendarYear]) would return a table of all years available (it even includes years that don’t have sales but this won’t alter the calculation). For each year in this table it returns the sum of salesamount, we need to use a measure here because it automatically calculates the sum no matter what context were are in. Then it uses this values of these sums to calculate an Average over.

The parameters are just like you say it, we want to calculate the average for each CalendarYear of Sum of SalesAmount.

This measure results in the following values:

The key here is the use of the first parameter supplied to the AverageX function, this is the table it will iterate over. Some other samples:

I want to calculate the Average sales amount of the “Contoso Bangkok No.1 Store”, we create a table by using the FILTER function where filter all storenames by one single store.

=AVERAGEX(FILTER(all(DimStore[StoreName]),
	DimStore[StoreName] = "Contoso Bangkok No.1 Store"),
	FactSales[Sum of SalesAmount])

I want to calculate the Average sales amount of the “Contoso Bangkok No.1 Store” and “Contoso Seoul Store”:

=AVERAGEX(FILTER(all(DimStore[StoreName]),
	DimStore[StoreName] = "Contoso Bangkok No.1 Store"
	|| DimStore[StoreName] = "Contoso Seoul Store"),
	FactSales[Sum of SalesAmount])

I want to calculate the average salesamount of all years leading up to the current year. First it checks if the current year has values, 2011 doesn’t have values but is available in the dimdate table. As table we want to return all years from the dimdate table before the current year. The current year is selected by values(DimDate[CalendarYear]) because we have set CalendarYear on the y-ax.  We need to check if values returns more than one result because when the subtotal is calculated values will return all years.

=if(FactSales[Sum of SalesAmount] > 0
	,AVERAGEX(
		FILTER(all(DimDate[CalendarYear]),
					DimDate[CalendarYear]
					<= if(countrows(values(DimDate[CalendarYear])) > 1
						,Blank()
						,values(DimDate[CalendarYear]))
				)
		, FactSales[Sum of SalesAmount])
	, Blank())

The AverageX, CountX and SumX functions are very powerful but difficult question, try to visualize what you want to do or use Excel to get the result you want and work from there.