Conditional Subtotal of Pivot Table

[#1]

Hi QV Community folks,

I am facing some problem with pivot table subtotals. Here is the screen shot of my current pivot table

I wiped out the sensitive information, but what I want is that for each year (second dimension) my subtotal calculates the AVERAGE of the "performance points" column for all rows except for the rows with 'N/A'. Is there a way to accomplish this? The "performance points" is a calculated fields with nested if statement because of the complexity of the determination of the points  

Thank you so much for any helpful guidance!

Best Regards,

Jihan

[tresesco]

May be like:

=

If(

Dimensionality<>1,

  RangeSum

  (

if(Denominator=0,'N/A',if(if(HPIP_Max<>HPIP_Min,1+(1-(HPIP_Max/100-Performance)/(HPIP_Max/100-HPIP_Min/100))*4,
if(Performance>=HPIP_Min/100,5,0))<0.99,0,if(if(HPIP_Max<>HPIP_Min,1+(1-(HPIP_Max/100-Performance)/(HPIP_Max/100-HPIP_Min/100))*4,
if(Performance>=HPIP_Min/100,5,0))>5,5,if(HPIP_Max<>HPIP_Min,1+(1-(HPIP_Max/100-Performance)/(HPIP_Max/100-HPIP_Min/100))*4,
if(Performance>=HPIP_Min/100,5,0)))))

  )

, RangeAvg

  (

    if(Denominator=0,'N/A',if(if(HPIP_Max<>HPIP_Min,1+(1-(HPIP_Max/100-Performance)/(HPIP_Max/100-HPIP_Min/100))*4,
    if(Performance>=HPIP_Min/100,5,0))<0.99,0,if(if(HPIP_Max<>HPIP_Min,1+(1-(HPIP_Max/100-Performance)/(HPIP_Max/100-HPIP_Min/100))*4,
    if(Performance>=HPIP_Min/100,5,0))>5,5,if(HPIP_Max<>HPIP_Min,1+(1-(HPIP_Max/100-Performance)/(HPIP_Max/100-HPIP_Min/100))*4,
    if(Performance>=HPIP_Min/100,5,0)))))

    )

)

[#3]

I was able to get it work by using avg(agg) function

Thanks so much!