# Measure: Calculate customers for a city

## Question

Sonnfan on Sun, 30 Dec 2012 17:34:08

Hi everybody, I need some help in creating a measure in MDX.

My customer dimension looks like that:

|-State
|---City
|-----Street
|--------Street number
|-----------Customer

So for example

|-Saxony
|-Bavaria
|---Munich
|-----Example-Street
|-------1
|-----------Customer A
|-------2
|-----------Customer B
|-----------Customer C
|-------3
|-----------Customer D

Now I'd like to create a measure, that gives me to every level the amount of customers, for example '4' for Munich (and something like 10 for Bavaria).

I read about the count function but can't seem to make it work properly. Could somebody tell my how to implement this feature? :)

Regards and best wishes,
Sonnfan

## Replies

Martin Mason on Sun, 30 Dec 2012 19:24:08

The following definition should work.

```CREATE MEMBER CurrentCube.[Measures].[CustomerCount] AS
COUNT(Descendants( [Customer].[Hierarchy].CurrentMember, [Customer].[Hierarchy].[Customer] ) )
```

HTH, Martin

http://martinmason.wordpress.com

Sonnfan on Tue, 01 Jan 2013 17:49:53

Hey, thanks for your suggestion. I was able to solve the Problem with the following code:

with member [Measures].[Amount] as 'sum(descendants([Customer].currentmember, [Customer].[Name]),1) '

...

Regards,
Sonnfan

Latest posts in the category

azure pack

sql server search