Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have the following table
Column1 | Column 2 | Column3 | Header 4 | Header 5 | |||
---|---|---|---|---|---|---|---|
A | 10 | 100 |
| ||||
B | 20 | 200 |
| ||||
C | 30 | 300 |
| ||||
Total |
|
| 20( Expected:
|
Column 2 : is a SUM(AGGR(SUM [.....])))
Column 3 : is a Average of some value AVG(AGGR(AVG[...]))
Expression in Header 4 : (Column 2 * Column 3)/(Total of Column 3 )
So my problem is in Header 4 : the total basically shows me Column 2 * Column 3 /Column 3
but what i was is the sum of the values in Header 4
Can anyone help me out here?
Thanks,
Jacob
Jacob,
Please see the attached. It should give you what you're looking for.
sum(aggr(sum(aggr(avg(Sales)*Sum(Target),Country))/sum(aggr(Sum(TOTAL Target),Country)),Country))
If you're using a straight table, for Expression 4, change the Total Mode to Sum of Rows instead of Expression Total:
Hi Nicole , i am using a pivot table and using partial sum. and the total in the partial sum is messed up
I believe Partial Sums always use expression totals, so you'll either need to mess around with your expression until it gives you the value you want (by summing the rows), or use a straight table instead.
Hi
You will need something like this (assuming Column1 is the single dimension in the table):
Sum(Aggr(
Sum(Aggr(Sum...)) * Avg(Aggr(Avg...)) / Sum(TOTAL Aggr(Avg(Aggr(...)))),
Column1))
The bold sections are your column2 and column3 expressions. All the chart dimensions need to be included in the dimensions of all 5 of the aggr() expressions.
HTH
Jonathan
Jonathan, i have attached an sample, it doesnt work. Sorry but do you mind helping me out here?
Hey Jacob,
Why should the total of column4 be 23.3333.
per your explanation it should be (20 * 600) / 600 = 1200/600 = 20 isn't it?
Thanks
AJ
Jacob,
Please see the attached. It should give you what you're looking for.
sum(aggr(sum(aggr(avg(Sales)*Sum(Target),Country))/sum(aggr(Sum(TOTAL Target),Country)),Country))
Thanks a lot nicole