How to calculate the sum of a group in MDX

Question

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

Imagine that in my structure OLAP I have the City and Dealer Dimensions, and a Sales Fact. In the City dimension I have City, State and Population, and this Population is a measure in one dimension, making the city dimension a Fact and a Dimension at the same time.

I need to add the Population in the cities from which a dealer had sales. But I can’t just add up the population of cities to sales, I can only show the total of the state. Remembering that a seller can sell in several cities and in more than one state.

I Filtreed by John dealer and the query returned that:

---

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 total population of California.

---

The calculation I’m currently using is

SUM([Measures].[Population])

I don’t have advanced knowledge in MDX, I couldn’t put together the calculation yet just that:

SUM(FILTER([Measures].[Population],[Sales Amount]>0))

or this

SUM(NONEMPTY((  [CidadesRadiografia].[Cidade].[Cidade],
                [Measures].[População],
                [RepresentantesRadiografia].[Representante].[Representante] )))

---

In short, I need to return only the sum of the population to the cities that the dealer had sales, but currently it shows the total population, for example, if I select the country and the city, returns the sum of the total of the country, and if I select the state and the city, returns the sum of the state’s population.

---

Question in the Stackoverflow

Answer 1

Now after reading the book SQL Server 2008 MDX Step by Step I was able to put together a calculation that is returning what I needed, it is performing well in SQL Server Management Studio and Visual Studio Data Tools, but in Excel it is not very performative, but this is not the case. the MDX calculation is as follows::

CREATE MEMBER CURRENTCUBE.[Measures].[Populacao]

AS SUM(NONEMPTY((Filter([Measures].[População],[Measures].[Valor Total] >0), 
   FILTER([CidadesRadiografia].[Cidade].[Cidade],[Measures].[Valor Total] > 0)))), 

FORMAT_STRING = "#,##0;-#,##0;0;0", 
NON_EMPTY_BEHAVIOR = { [Valor Total] }, 
VISIBLE = 1;

I advise the book above for someone who as I had never worked with MDX.