Sum function returning average (pivot table)

[#1]

Hello,

I have the below function in a pivot table to calculate the ARS (average rate of sale) at higher levels.

However, when the ARS is subtotaled, the value is not the sum of all value. Instead, it recalculates in the subtotal line.

I know i can switch to a straight table and select the sum of rows option. Is there anyway to achieve the same result in a pivot table?

sum (ars * net sales) / sum (net sales)

Thanks,

Sara

[swuehl]

Hi Sara,

I think it should be possible using advanced aggregation. There is an explanation and examples in the help file:

"Sum of rows in pivot tables"

Your expression could look like:

= sum(aggr(Sum(ars * [net sales]),YOURDIMENSION)) / sum(aggr(sum([net sales]), YOURDIMENSION))

Regards,

Stefan

edit: You probably don't need the sum(aggr(...)) for the second part, if the evaluation as total expression is fine here.

edited by swuehl

[#3]

That returned the same results. the total for vendors b,c,d,e should be 6.5648.

Vendor	Net Sales	ARS
a	8	0.2694
Total	8	0.269
b	1	0.076
c	16	0.473
d	1	0.084
e	41	5.9318
Total	59	4.253

I've also tried this expression, which i got from another post...the only difference is that it returned 0 subtotals.

rangesum(above(sum(ars * net sales) / Sum(net sales),0, rowno(Total)))

Vendor	LW Units	ARS
a	8	0.2694
Total	8	0
b	1	0.076
c	16	0.473
d	1	0.084
e	41	5.9318
Total	59	0

[swuehl]

How do you group your Vendors? Do you use another dimension not shown here?

Could you post your qvw file here or a sample that reproduces your problem?

[#5]

Vendors are grouped by department, departments by group, and groups by divisions.

[swuehl · Accepted solution]

Try this:

=sum(aggr(sum([Net Sales]*ARS) /sum( [Net Sales]),Vendor,Department,Group,Division))

edit:

=sum(aggr(sum([Net Sales]*ARS) /sum( [Net Sales]),Vendor))

should be enough, I think

edited by swuehl

[#7]

Perfect, that worked. Thanks sooo much!!!