Sub-total of part-of dimensions in Table (ignore some other dimensions)

[tracyng0905]

Hi,
I have a table like below with multiple dimensions and value (Amount). What I want to do is to calculate the sub-total by each member and category, here is my expression but it return incorrect result. Can someone pls help me?

Cat Sub Total = Calcaulate(SUM(Amount), ALL(Sub-Cat), ALL(Brand),ALL(Product), ALL(Amount))

Member	Category	Sub-Cat	Brand	Product	Amount	Cat Sub Total
M001	C001	SC001	B001	P001	100	1000
M001	C001	SC002	B002	P002	200	1000
M001	C001	SC003	B003	P003	300	1000
M001	C001	SC004	B004	P004	400	1000
M002	C002	SC005	B005	P005	150	350
M002	C002	SC005	B006	P006	200	350
M002	C003	SC006	B007	P007	100	600
M002	C003	SC007	B008	P008	300	600
M002	C003	SC007	B009	P009	200	600

Regards,

TN

[Anonymous]

You have not stated if you're after a measure or a calculated column in one of the base tables... or in a completely new table...