Pivot table Average calculation

[ramyasaiqv]

Hi All,

I’m trying to add and average 3 columns in a pivot table and having  issue and I need some assistance in resolving my issue.

I have 3 expressions exp1, exp2 and exp3 and 4^th expression is Total Average of the 3 columns (exp1+exp2 + exp3/3).

If all the 3 expressions have data the “Total average” is calculated correct. If any of the expression is null, or ‘ – ‘ then  total average is calculated as ‘-‘.

But what I want is if one of the value is null or ‘ –‘   it should ignore that value and average the available values.

For example in the attached qvf and excel   Q2-2016 data for   TXT and MXT is ‘-‘but RXT Q2-2016 the value for Item ‘ KLS’ is 3.30 and  the Total average is calculated as ‘-‘. But the expected result is 3.30 because there is only one value available.

Eg2: For Item ‘XLS’ 2017-Q2 (MXT) is ‘-‘ and TXT and RXT has 4.0 and  2.0 respectively . So the result should be (4+2)/2=3.0 instead of ‘-‘.

Please help me in resolving this issue.

I have attached the qvf and excel with sample data for your reference.  Thank you,

Ramya

[sunny_talwar · Accepted solution]

Try this

RangeAvg(Column(1), Column(2), Column(3))

[ramyasaiqv]

Thank you so much for the help Sunny.

Regards,

Ramya