How do I calculate the sum total of a group in MDX?

Question

I need to create a Calculation in the SQL Server Data Tools.

Imagine that in my OLAP structure I have a City and a Seller Dimensions, and a fact. And in the City Dimension I have a city, a state, and a Population, and this is a Measure in a Dimension.

I need to sum the Population in the cities which have sales. But I can't sum only the cities with sales, I can only show the state's total population. One seller can sell in many cities. Example:

I'll filter by seller John and the query returns this:

---

STATE  |   CITY            |     POPULATION 

CA     |   Los Angeles     |     10.000.000
CA     |   San Francisco   |      1.000.000  
CA     |   Sacramento      |      1.000.000   
CA     |   San Diego       |      1.000.000   
CA     |   Bakersfield     |        500.000 

Total                          37.000.000

---

The sum should return 13.500.000, but for me, the sum returns 37.000.000, which is the population of CA.

I don't have advanced knowledge in MDX, I can't create a calculated member with this context and can't provide examples.

---

In short, I need to return only the sum of the population of the cities in which this seller has sales, but currently is returning the sum total of the population, for example, if I select to show the country and the city, returns the sum total of the country's population, if I select to display the state and the city, returns the sum total of the population of the state

Answer 1

If you just want to show the sum of population by seller and cities, you can use something like the query below.

SELECT Measures.Population 
ON 0,
FILTER(Seller.SellerName.CHILDREN * City.City.CHILDREN, Measures.[Sales Amount] > 0)
ON 1
FROM [YourCube]

OR

SELECT Measures.Population 
ON 0,
(Seller.SellerName.CHILDREN * City.City.CHILDREN)
HAVING Measures.[Sales Amount] > 0
ON 1
FROM [YourCube]

Obviously, you would have to substitute with the actual dimension names from cube.

EDIT:

If you just wanted the sum of population in all the cities where the seller has sales, try the code below

//Build a set of cities
with set CitiesForSeller as
exists(City.City.CHILDREN, strtoset('Seller.SellerName.&[SomeName]'), "<<Name of the measure group which has the population measure>>")

//Get the sum of population in all the cities combined
member measures.SumOfPopulation as
sum(CitiesForSeller,Measures.Population)

select measures.SumOfPopulation on 0,
CitiesForSeller 
having measures.SumOfPopulation > 0
on 1
from [YourCube]