Cal Measure % against total

How to define calculated member whose value should be the row value divided by total of all rows. Example, sales of 2005 was 50k and sales for 2006 was 100k. The % 2005 sales is 33% of total (150k) and 2006 is 67%.

Hi

For example to get the percentage of the Unit Sales for one customer of all customers, you write:

[Measures].[Unit Sales] / ([Measures].[Unit Sales], [Customers].[All Customers])

The key is, to divide by a MDX tuple ([Measures].[Unit Sales], [Customers].[All Customers]) which brings the value for all customers.

Hans

|||

Thank Hans

This problem is solved. Please guide me to get the same result with multiple dimensions.

Also, i have a variance calculated member, which is on the date time diminsion [Sale of 2005] - [Sale of 2006] = [Variance 2005]. I would like to see three measures, [Year 2005 Sale], [Year 2006 Sale] and variance. But i get Sales and variance figure under Year 2005 and same under Year 2006. How to get the desired result.

Thanks

Shekhar

|||

Hi Shekhar,

I'm not sure, if I did understand you right, but I think it's because your [Variance] is on the Time Dimension. I do it in my projects so, that I create a calculated member like:

MEMBER [Measures].[Year variance] AS ([Measures].[Unit Sales],[Time].Currentmember.Prevmember) - [Measures].[Unit Sales]

If Currentmember is Year 2007, den Prevmember is Year 2006 and so on.

If you use now all 3 in a select, you can see it "flat"

select

{ [Measures].[Year 2005 Sales], [Measures].[Year 2006 Sale], [Measures].[Year variance]} on columns,

.....

Hans