Correct Total for Calculated Measure

hello everyone!

i'm having basic problem calculating measure not rolling correctly. essence (borrowed): 

[total sold] = [measures].[quantity] * [measures].[unit price]

total product             30         200         6000-->should 3000 
 
product a                  10         100         1000 
 
product b                  20         100         2000

i can compute correct total if define custom dimension member correct solve_order. however, want correct results in excel, , don't have control on excel's queries.

thanks!

original place question asked: http://www.eggheadcafe.com/forumarchives/sqlserverolap/dec2005/post25056620.asp

hi

the problem experiencing formula being applied @ total level. assume want have formula applied @ product level , higher levels sum of children.

to have couple of options:

1. add new named calculation dsv calculates total sold each row , add cube measure (probably easiest).
2. add new calculated member cube using iif , rollupchildren value. below envisage like:

create member currentcube.[measures].[total sold]     iif(isleaf([product].currentmember), [measures].[quantity] * [measures].[unit price],  		rollupchildren([product].currentmember, "+")) ,     format_string = "currency",  non_empty_behavior =    {     [measures].[quantity],    [measures].[unit price]   } ; 

---

craig bryden - please mark correct answers

SQL Server  >  SQL Server Analysis Services