Answered by:
SSAS Calculated Member
Question

Hi,
I have the below case where I need to have sum of sales with respect to product as shown below.
I have 3 dimension, selection any of which the Sum sales columns should give the total sales for each product. This formula should be applicable even if two dimensions are selected.
Please let me know if anyone has Idea on it.
Thanks in Advance.. :)
Answers

This is fine. But what happens if we swap the dimensions. I want a general solution which should work even if we swap the dimension it should work.
I got solution for this.
Thanks,
Rajesh S Hegde
 Edited by Rajesh S Hegde Tuesday, May 3, 2016 12:45 PM
 Marked as answer by Rajesh S Hegde Tuesday, May 3, 2016 12:45 PM
All replies

Hi Rajesh,
In MDX, you can create a calculated member, which is a member based on other members. You can define two kinds of calculated members: ones that are measures and ones that are not. So in your scenario, we can achieve the requirement by add a calculated member. Here is a sample query.
WITH MEMBER Measures.x AS SUM ( {[Date].[Calendar].currentmember*[Date].[Month of Year].members} , [Measures].[Internet Sales Amount] )/2 select {[Measures].[Internet Sales Amount],Measures.x} on 0, nonempty({[Date].[Calendar].[Calendar Year].members*[Date].[Month of Year].members}) on 1 from [Adventure Works];
Results:
Reference:Overview of Calculated Measures and Members
Regards,
Charlie Liao
TechNet Community Support 
This is fine. But what happens if we swap the dimensions. I want a general solution which should work even if we swap the dimension it should work.
I got solution for this.
Thanks,
Rajesh S Hegde
 Edited by Rajesh S Hegde Tuesday, May 3, 2016 12:45 PM
 Marked as answer by Rajesh S Hegde Tuesday, May 3, 2016 12:45 PM