# Conditional sum or average as measure in PowerPivottable

• ### Question

•  Dear

I would like to create a report (PowerPivotTable) where the 'Grand total' column consists of a mix of sums and averages of the measures if certain conditions are true or false. In next example there are three products, product A, B & C. For products A & C I would like to have the sum of the sales, but for product B I would l like to see the average.

I'm convinced this possible with PowerPivot but I could not find the correct formula or an example on the net.

 Product Period Sales prodA 201101 10 prodA 201101 20 prodA 201102 30 prodA 201103 40 ProdB 201101 15 ProdB 201102 25 ProdB 201103 35 prodC 201101 11 prodC 201102 21 prodC 201103 31 prodC 201104 41

Report (PowerPivottable)

 Row Labels 201101 201102 201103 201104 Total/Avg prodA 30 30 40 100 ProdB 15 25 35 25 prodC 11 21 31 41 104

Eddy N.
Monday, November 7, 2011 9:13 AM

• Javier

At first I was confused because I kept both measures Sales and Measure1 in the detail area. After removing the Sales measures I got exactly what I wanted.

Eddy N.
• Marked as answer by Monday, November 7, 2011 11:11 PM
Monday, November 7, 2011 11:11 PM

### All replies

• Hi Eddy

You can use the following calculation:

```=IF(COUNTROWS(VALUES(Table1[Product]))=1,
IF( VALUES( Table1[Product] ) = "ProdB", AVERAGE( Table1[Sales] ), SUM( Table1[Sales] ) )
)```

Javier Guillen
http://javierguillen.wordpress.com/
Monday, November 7, 2011 3:58 PM
• Javier