Cant use THen OVER on calculated values

[anthony estrada]

Hi,

 

Im trying to use sum then over clause on calculated value on text area. but it is error. this is my formulaSum([pass]) / Sum([total]), Sum([pass]) / Sum([total]) THEN Product([Value]) OVER (All([Axis.Rows])). I also noticed that the grand total is incorrect. Please see the attached dxp and image. from the sample below i need to get the product of the 3 process into the calculated value.

 

 

thanks in advance.

[Khushboo Rabadia]

Grand total is calculated on underlying rows of data. If you have observed your Sum([Pass]) total value is 88 and your Sum([Total]) is 238 so 88/238 = 36.97 which is correct

You are expecting it to show 5.63% but in cross table you are getting that value as vertical axes have process column. So your data is divided by that column ie. shown for each process. If you remove process from vertical axes, cell values custom expression is applied on the total data as seen in textarea which will always give 36.97

If you want to show 5.63% i.e. product by each process you will need to include it in your expression and for doing product extract distinct values:

Product(distinct Sum([pass]) OVER (Intersect([process])) / Sum([total]) OVER (Intersect([process])))

[anthony estrada]

Hi Khushboo, Thanks a lot on this one. it is now working. last question, how to make this work on cross tables i tried the same formula on cross table but not returns the correct value. thank you Aze