CODEWITHSUNDEEP
mdxaverage
user3235704
user3235704

Reputation: 1

MDX query and calculated members for two different averages in same query

I have an MDX/calculated member question here. It has been a while since I've done this and have forgotten a lot. I have a cube with the following dimensions and levels:

Sites

Clients

I have a measure

What I want to show/return from an MDX query is the following:

Site | Prod Count | Avg Prod Count Across All Sites for Current Client | Avg Prod Count

Across All Sites in Current Client's Industry

Example Data:

Site      | Prod Count   | Avg 1          | Avg 2

Site 1    | 100          | 50             | 200  
Site 2    | 125          | 50             | 200  
Site 3    | 112          | 50             | 200

What I'm trying to figure out is how or if I can use 2 different calculated members to calculate the averages above.

The challenge is that the query has to be in the following format because I'm using a reporting tool and it is generating the MDX.

`SELECT
{
[Measures].[Product Count],
[Measures].[Calc Avg 1],
[Measures].[Calc Avg 2]
} ON COLUMNS,
{[Sites].[Site Name].[Site Name].Members} ON ROWS
FROM [Cube]
where ([Clients].[Client Name].&[Client A])`

So basically, my question is:
What would be the proper way to define the averages I'm looking for using calculated members?

Whenever I try it out I'm only able to calculate the average product count across all sites for the current client, but I'm not able to get the average across all sites in the current client's industry.

Upvotes: 0

Views: 189

Answers (1)

Bill Anton
Bill Anton

Reputation: 2970

here's an example using adventure works to get you started. the calculated members will need to be ported to the MDX script to use with your tool. Here's the mapping:

City = "Client Site" State = "Client" Country = "Client Industry"

WITH
    MEMBER Measures.ClientCitiesCount AS
        Exists(
             [Customer].[City].[City] // represents client sites
            ,[Customer].[State-Province].CurrentMember // represents client
        ).Count
    MEMBER Measures.ClientCitiesSales AS
        SUM(
             [Customer].[State-Province].CurrentMember
            ,[Measures].[Internet Sales Amount]
        )
    MEMBER Measures.AvgAcrossClientCities AS
        ClientCitiesSales/ClientCitiesCount

    MEMBER Measures.IndustryCitiesCount AS
        Exists(
             [Customer].[City].[City] // represents industry sites
            ,Exists(  
                 [Customer].[Country].[Country] // represents client's industry
                ,[Customer].[State-Province].CurrentMember // represents client
            )
        ).Count

    MEMBER Measures.IndustryCitiesSales AS
        SUM(
             Exists(
                 [Customer].[Country].[Country]
                ,[Customer].[State-Province].CurrentMember
             )
             ,[Measures].[Internet Sales Amount]
        )
    MEMBER Measures.AvgAcrossIndustryCities AS
        IndustryCitiesSales/IndustryCitiesCount
SELECT
    {
         [Measures].[Internet Sales Amount]
        ,ClientCitiesCount
        ,ClientCitiesSales
        ,AvgAcrossClientCities
        ,IndustryCitiesCount
        ,IndustryCitiesSales
        ,AvgAcrossIndustryCities
    } ON 0,
    {
        [Customer].[City].[City] // represents client sites
    } ON 1
FROM
    [Adventure Works]
WHERE
    [Customer].[State-Province].&[GA]&[US] // represents client

Don't forget to add in some edge-case handling (e.g. IIF the client has 0 "sites" in context) and consider using the "measuregroup" parameter in the EXISTS function.

Upvotes: 0

Related Questions