Get share of dimension member in calculated measure

Question

Not sure if what I'm trying to do is possible or if I need to change my data model.

I have a dimension containing the different amouts a customer can loan so what I wan't to do is see the share of a certain amount compared to total sales.

Pseudo code:

member [Measures].[Share 5000] as 'count([Amount].[5000])/([Measures].[Total Sales], [Time].CurrentMember)'

Answer 1

I assume you want the 5000 included included in your calculation? So if 10 consumers loaned 5000 and the total sales is 100000 the share is (5000*10) / 100000 = 0,5

First you will need a 'number of customers' measure, I don't know if it exists already in your data model otherwise you will have to add it.

Then you can write your calculation something like this:

member [Measures].[Share 5000] as
'
(([Amount].[Total Amount].[5000],[Measures].[Number of Customers])*5000) / 
([Amount].[Total Amount],[Measures].[Total Sales])
'

You don't need to include the Time.CurrentMember in your calculation as it does not make any difference in this case. If you put Time in the rows or columns it will be automatically included.