CODEWITHSUNDEEP
sql-server-2012ssasmdx
whytheq
whytheq

Reputation: 35557

Find Percent of Parent

The following script aims to return three fields:

1.Region
2.Revenue
3.%Parent ...the structure of our geo dimension this will be the region's revenue as a percent of the continents revenue. 

WITH    
MEMBER Measures.[%Parent] AS 
    [Measures].currentmember/
    (
    [Geolocation].[Geography].currentmember.parent.name, 
    [Measures].currentmember
    ), format_string ="Percent"
SELECT
    ORDER(
        DESCENDANTS(
            [Geolocation].[Geography].[All],
            [Geolocation].[Geography].[Geography Region]
            ),
        [Geolocation].[Geography].CurrentMember.name, 
        BASC 
        ) ON ROWS,
    {[Measures].[Revenue], Measures.[%Parent]}  ON COLUMNS
FROM [MyCube]

Currently it returns the following. How do I change the script to give me the percentages I want?

enter image description here

Upvotes: 2

Views: 186

Answers (1)

findango
findango

Reputation: 1483

I think [Measures].CurrentMember is probably wrong -- it refers to the calculated member. Try referring to the measure explicitly:

WITH MEMBER [Measures].[%Parent] AS
    [Measures].[Internet Order Quantity] / 
    ([Customer].[Customer Geography].CurrentMember.Parent, [Measures].[Internet Order Quantity])
    , format_string ="Percent" 
SELECT
    {[Measures].[Internet Order Quantity], [Measures].[%Parent]} ON 0,
    DrillDownLevel([Customer].[Customer Geography].[All Customers]) ON 1
FROM [Adventure Works]

Results:

               Internet Order Quantity            %Parent
All Customers                   60,398             1.#INF
Australia                       13,345             22.10%
Canada                           7,620             12.62%
France                           5,558              9.20%
Germany                          5,625              9.31%
United Kingdom                   6,906             11.43%
United States                   21,344             35.34%

(of course my quick example doesn't guard for the All member, but you get the idea.)

Upvotes: 2

Related Questions