# Sum Totals/Average in Power Pivot

• ### Question

• Hello,

I have created some calculated tables in powerpivot based on sales data. I have arranged my products vendors etc into a heirarchy and the pivot sums to sales volumes accordingly for each week. My problem is that I need to total column at the end to show an average sales value for each week rather than summing it (currently it adds the values for week 1,2,3 and 4 etc). Can I do anything about this in powerpivot - when I look in field settings the option for average is greyed out?

Thanks if anyone can support with this.

Monday, January 5, 2015 6:42 PM

• Use this measure:

```AverageXSumTotalSales:=
AVERAGEX(
VALUES( Forecast[Week Number] )
, SUM( Forecast[Total Sales Value] )
)```

AVERAGEX() iterates over the rows of the table passed to it and evaluates the expression passed as its second argument for each row in that table, adding each value to a running total, and then divides by the number of rows in the table.

The table we pass is VALUES( Forecast[Week Number] ). VALUES() returns a list of all the unique values which make up the column passed to it, thus in a detail column in the pivot table it is a 1x1 table. Any averaging we do here is the same as just evaluating the second expression in AVERAGEX() (a simple sum).

At the total level, it calculates the average of the values of the SUM() for each week.

• Marked as answer by Tuesday, January 6, 2015 12:15 AM
Monday, January 5, 2015 8:47 PM

### All replies

• Grinch,

You need to add a calculated field (also called Measure) which used AVERAGE instead of SUM.

Average Sales:=AVERAGE(Sales[SalesAmount])

Regards, Avi www.powerpivotpro.com

Monday, January 5, 2015 6:46 PM
• Mt apologies - I have attached a file with a text box explaining to make it clearer. It is just the grand total column at the end that needs to use average.

Monday, January 5, 2015 8:35 PM
• Monday, January 5, 2015 8:36 PM
• Use this measure:

```AverageXSumTotalSales:=
AVERAGEX(
VALUES( Forecast[Week Number] )
, SUM( Forecast[Total Sales Value] )
)```

AVERAGEX() iterates over the rows of the table passed to it and evaluates the expression passed as its second argument for each row in that table, adding each value to a running total, and then divides by the number of rows in the table.

The table we pass is VALUES( Forecast[Week Number] ). VALUES() returns a list of all the unique values which make up the column passed to it, thus in a detail column in the pivot table it is a 1x1 table. Any averaging we do here is the same as just evaluating the second expression in AVERAGEX() (a simple sum).

At the total level, it calculates the average of the values of the SUM() for each week.

• Marked as answer by Tuesday, January 6, 2015 12:15 AM
Monday, January 5, 2015 8:47 PM
• Thanks very much,

I can use that but is there any way I can just see it once. When I add it in I get an average after each week, I just want to see the current running average ideally so only want to see it once. I can live without though.

Also, I wish to add a KPI to this measure I have different targets depending on the family. Example below, I know if I had an absolute target that I could add this in but can it be done if different categories have different targets?

 Family Target for Average Sales Value] ACCESSORY 2052120 BROADBAND 16000 CARDS 117720 DISPLAY UNIT 2640 GIFT WITH PURCHASE 315200 HANDSET 4791560 LICENCES 12760 MOBILE BROADBAND 71280 MSACCESSORY 4760 MSHANDSET 8680 MSTABLET 6280 SALETABLET -2000 SIM 15591400 TABLET 207880 WEARABLE 19160

Monday, January 5, 2015 9:19 PM
• Hi,

I have figured this out now - didnt want anyone spending their time on it now I have it.