CODEWITHSUNDEEP
mdxolapiccube
Gustavo Moreno
Gustavo Moreno

Reputation: 55

MDX calculation with children and parents (contribution to total)

I got a cube with cadastral information of some neighborhoods of a city. One of the measures is 'cadastral value': the value of the land of a neighborhood. Now, the city is divided into districts and a district, into neighborhoods (hierarchy with two levels).

The question is: I need to calculate the percentage that the value of each neighborhood represents compared with the parent (the district) and with the total (city), and also the same about the district compared with the city.

How can I do it?

I tried:

WITH MEMBER
[Measures].[Percentage] AS 
  ([Nbh].[Nbh].[Nbh],[Measures].[Cad value].CurrentMember
   /[Nbh].[Nbh].[Nbh],[Measures].[Cad value].CurrentMember.Parent)

SELECT 
 {[Measures].[Percentage]} ON 0,
 [Nbh].[Nbh].[Nbh].AllMembers ON 1
FROM Cadastre

Where the dimensions are:

Nbh : Neighborhood

Cad value: Cadastral value

What I get is "error" in every result value, and the explanation is "Current member () : argument (0) type mismatch: expected:'level|hierarchy|dimension', got:'measure'"

What am I doing wrong?

Upvotes: 1

Views: 1468

Answers (3)

pirmas naujas
pirmas naujas

Reputation: 300

The error is certainly because of this statement is incorrect:

WITH MEMBER
[Measures].[Percentage]
AS ([Nbh].[Nbh].[Nbh],[Measures].[Cad value].CurrentMember/[Nbh].[Nbh].[Nbh],[Measures].[Cad value].CurrentMember.Parent)

Try to make it like this

WITH MEMBER
[Measures].[Percentage]
AS ([Nbh].[Nbh].[Nbh],[Measures].[Cad value])/([Nbh].[Nbh].[Nbh],[Measures].[Cad value])

Upvotes: -1

whytheq
whytheq

Reputation: 35557

Try the following:

WITH MEMBER [Measures].[Parent %] AS 
   IIF(
    [Nbh].[Nbh].CURRENTMEMBER IS [Nbh].[Nbh].[All]
    ,1
    ,[Measures].[Cad value] /
     (
      [Measures].[Cad value],
      [Nbh].[Nbh].CurrentMember.Parent
     )
   ), FORMAT_STRING = 'Percent'
SELECT 
 {[Measures].[Parent %]} ON 0,
 [Nbh].[Nbh].[Nbh].AllMembers ON 1
FROM Cadastre;

Including the IIF is a defensive measure. If you switch to showing AllMembers of the complete hierarchy then the All member will be included. Here is an example using AdvWrks:

WITH 
  MEMBER [Measures].[Parent %] AS 
    IIF
    (
        [Product].[Subcategory].CurrentMember
      IS 
        [Product].[Subcategory].[All Products]
     ,1
     ,
        [Measures].[Internet Sales Amount]
      / 
        (
          [Measures].[Internet Sales Amount]
         ,[Product].[Subcategory].CurrentMember.Parent
        )
    ) 
   ,FORMAT_STRING = 'Percent' 
SELECT 
  NON EMPTY 
    {[Measures].[Parent %]} ON 0
 ,NON EMPTY 
    [Product].[Subcategory].ALLMEMBERS ON 1
FROM [Adventure Works];

The above returns the following:

enter image description here

Without the IIF the expression is the following:

WITH 
  MEMBER [Measures].[Parent %] AS 
      [Measures].[Internet Sales Amount]
    / 
      (
        [Measures].[Internet Sales Amount]
       ,[Product].[Subcategory].CurrentMember.Parent
      ) 
   ,FORMAT_STRING = 'Percent' 
SELECT 
  NON EMPTY 
    {[Measures].[Parent %]} ON 0
 ,NON EMPTY 
    [Product].[Subcategory].ALLMEMBERS ON 1
FROM [Adventure Works];

We get the following because of lack of foresight:

enter image description here

Upvotes: 1

SouravA
SouravA

Reputation: 5243

WITH MEMBER [Measures].[Percentage] AS
([Nbh].[Nbh].[Nbh],[Measures].[Cad value].CurrentMember /[Nbh].[Nbh].[Nbh],[Measures].[Cad value].CurrentMember.Parent)

There are two problems with this:

One, you are using a static set to compute the value of calculated member namely: [Nbh].[Nbh].[Nbh] This is NOT [Nbh].[Nbh].CURRENTMEMBER

Second and the bigger mistake: Using the currentmember on a measure. You can't do that. You can use Measures.CURRENTMEMBER but that's about it. It's best used on hierarchies which are used on axes.

That said, your approach is almost legit. You need to fetch the measure value for the parent.

This should work:

WITH MEMBER
[Measures].[Percentage] AS 
DIVIDE
        (
        [Measures].[Cad value], 
        ([Nbh].[Nbh].CURRENTMEMBER.PARENT,[Measures].[Cad value]) //Cad value for parent
        )

SELECT 
 {[Measures].[Percentage]} ON 0,
 [Nbh].[Nbh].[Nbh].AllMembers ON 1
FROM Cadastre

Upvotes: 1

Related Questions