Getting percent total of group using MDX

Question

I have a SSAS cube where I am trying to calculate the percent total of a sales measure against a Location Hierarchy.

The location hierarchy has two members from Top to Bottom: State/Province > City.

Ex:

State     City
----------------------
Florida   West Palm Beach
Florida   Jacksonville
Florida   Miami
Georgia   Atlanta
Georgia   Macon
....

What I am trying to do is to calculate the percent total of a Measure called [Measures].[Sales] at a city level rolled up the hierarchy to its state. This new measure (called [Measures].[% Total]) would have a percent value (in decimal form) of sales for each city with respect to its state.

Ex:

State     City         $ Sales  % Total
--------------------------------------
Florida   West Palm Beach  100       0.25
Florida   Jacksonville     200       0.50
Florida   Miami            100       0.25
Georgia   Atlanta          200       0.5
Georgia   Macon            200       0.5
....

What is a good approach for doing this in an MDX query?

The general approach I have done is to do something like the below MDX PSUEDO-CODE, but I can't figure out how to group the records based on the State member. I want to divide the Sales by the total Sales for the currentmember City's State. I am trying to make this dynamic and avoid using any hardcoded member values.

-- Not Actual MDX Code

WITH MEMBER [Measures].[Sales Percent Total] as [Measures].[Sales] / ([Measures].[Sales] WHERE [Location Hierachy].[City].currentmember.parent = [Location Hierachy].[State].member) SELECT {[Measures].[Sales], [Measures].[Sales Percent Total]} ON COLUMNS, {[Location Hierachy].[City]} ON ROWS FROM [SalesCube];

score 0 · Answer 1 · answered Jul 21 '23 at 08:39

What you're looking for is something like this:

WITH 
  MEMBER [Measures].[Sales Percent Total] as ([Measures].[Sales], [Location Hierachy].CurrentMember) / ([Measures].[Sales], [Location Hierachy])
SELECT 
  [Location Hierarchy].[City].Members on Rows,
  { [Measures].[Sales], [Measures].[Sales Percent Total] }
FROM [SalesCube]

What this does is calculate the percent of sales as the fraction between the sales for the current city being evaluated divided by the sales for the entire dimension.

If you want to select only a subset of cities and display the percentage with regards to the total being returned you should modify the query:

WITH 
  SET CITIES as Filter( [Location Hierarchy].[City].Members, <some condition here>)
  MEMBER [Location Hierarchy].[Row Set] as Aggregate(CITIES)
  MEMBER [Measures].[Sales Percent Total] as ([Measures].[Sales], [Location Hierachy].CurrentMember) / ([Measures].[Sales], [Location Hierachy].[Row Set])
SELECT 
  CITIES on Rows,
  { [Measures].[Sales], [Measures].[Sales Percent Total] }
FROM [SalesCube]

And you need to replace <some condition here> by the condition you want to filter on, for example [Location Hierarchy].CurrentMember.Parent.Name ="Florida". In this example the percentage sales of a city will be calculated with respect to the total of the state.

Getting percent total of group using MDX

1 Answers1