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 need to create a calculated member with this context and can provide this example:
Sum
(
(
[Measures].[População]
,Filter
(
[RepresentantesRadiografia].[Representante].CurrentMember.Children
*
[CidadesRadiografia].[Cidade].CURRENTEMEMBER.Children
,
[Measures].[Valor Total] > 0
)
)
)
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
I asked that question, but got no answer that worked