How to write a expression

[naveen_kunuru]

Hello All,

                  I need help in writing a expression ,the below table as three dimensions

                                                                                           1)Material Code,2)Vendor code,3)Plant code

        and Four expressions    

                                             1)Residual:    sum({<[FISCAL YEAR]=>}Resi)

                                             2) VolumeFY2017(16-17):  Sum({<[FISCAL YEAR]={'$(=max([FISCAL YEAR])-1)'}>}VOLUME)                                                                3)    Column(1)*Column(2)

                                            4)Savings Potential :

sum(aggr(

Sum({<[FISCAL YEAR]={'$(=max([FISCAL YEAR])-1)'}>}VOLUME)*sum({<[FISCAL YEAR]=>}Resi])

,[MATERIAL CODE],[VENDOR CODE],[PLANT CODE]

))

So the problem is when i use column(1)*column(2) iam getting 29973.741(203.903*147) which is correct but

when i am trying to use along with aggr function i am getting Zero ,

so kindly any one suggestion what am i missing in my expression 4th, or is there any other way to write the above expression

Thanks

[naveen_kunuru]

i managed  to convert the zero value in to actual value by using below expression ,but the total i am getting is wrong

Expression:

(Sum({<[FISCAL YEAR]={'$(=max([FISCAL YEAR])-1)'}>}VOLUME)*sum({<[FISCAL YEAR]=>}Resi))

How to sum  rows

[Anil_Babu_Samineni]

What is the wrong here?

[naveen_kunuru]

Total,

It suppose to be sum of (2513340.18+29973.741=2543313.921)

but the total is 5033019.342

[Anil_Babu_Samineni]

Finally, The sub total will calculate over Total only for each measure, So for me it looks good, To get rid we need to use Aggr(). Off course, It's tough to say without having something. Will you attach sample?

[Anil_Babu_Samineni]

Please try to reply back ASAP or else it may wind out for long