locked
Conditional sum or average as measure in PowerPivottable RRS feed

  • 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

Answers

  • Javier

     

    Great! It works. Thank you for your advice.

    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 Eddy Nijs 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
    Answerer
  • Javier

     

    Great! It works. Thank you for your advice.

    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 Eddy Nijs Monday, November 7, 2011 11:11 PM
    Monday, November 7, 2011 11:11 PM