How to calculate the sum of a group in MDX

Navigation
0

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

Imagine that in my OLAP framework I have the Dimensions City and Reseller, and a Sales Fact. In the City dimension I have City, State and Population, and that 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 reseller had sales. But I can not add just the population of the cities with sales, I can only show the state total. Remembering that a seller can sell in several cities and in more than one state.

I filtered through the retailer John and the query returned 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 total population of California.

The calculation I currently use, is 

SUM([Measures].[Population])

I do not have advanced knowledge in MDX, I could not mount the calculation yet just this: 

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 reseller had sales, but currently it shows the total population, for example, if I select the country and the city, returns the total sum of the country , and if I select the state and city, it returns the sum of the population of the state.

Question no StackOverflow

    
asked by anonymous 17.12.2014 / 11:52

1 answer

0
Now after reading the SQL Server 2008 MDX Step by Step book I was able to put together a calculation that is returning what I needed, it is performing very well in SQL Server Management Studio and in Visual Studio Data Tools , but Excel 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 recommend the above book for someone who like me had never worked with MDX.

    
18.12.2014 / 10:42
iOS 8 - Manually Changing Core Location Permissions VSS 2005 Internet Access and IIS 7 - I can not configure for remote access