Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).

MDX - Calculating the Percent of a Named Set

Getting the percent of something in MDX seems like a pretty easy task but can be complicated when extra logic is needed to be built in.  In this example a named set has been added and the requirement is to bring back the top 10 product sales and display the percent of each across all products (including products that are not part of the top 10).  If you're not careful here you could easily return back the top 10 product correctly but show the percentage of the products sales across only top 10.  Here's a couple example of both ways.

This example shows the percentage for each product sales divided by the over all sales (Product Sales/All Product Sales)

With Set Top10 AS  
TopCount([Product].[Product].Children,  
10,
[Measures].[Reseller Order Count])   

Member [Measures].[PercentSold] AS  
([Measures].[Reseller Order Count])/([Product].[Product].[All Products],[Measures].[Reseller Order Count]), Format_String ="Percent" 

Select {[Measures].[Reseller Order Count],[Measures].[PercentSold]}  on 0,  
Top10 on 1  
From [Adventure Works] 


 This example shows the percentage for each product sales divided by the top 10 products sales (Product Sales/Top 10 Product Sales)

With Set Top10 AS  
TopCount([Product].[Product].Children,  
10,
[Measures].[Reseller Order Count])   

Member [Measures].[PercentTopSold] AS
[Measures].[Reseller Order Count]/SUM([Top10],[Measures].[Reseller Order Count]), Format_String ="Percent" 

Select {[Measures].[Reseller Order Count],[Measures].[PercentTopSold]}  on 0,  
Top10 on 1  
From [Adventure Works] 

 

 


 

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.