Calculating percentages in SQL Server can sometimes be a complex task, especially when additional logic needs to be incorporated. In this article, we will explore an example that involves calculating the percentage of product sales in MDX (Multidimensional Expressions) queries.

Let’s consider a scenario where we have a named set that represents the top 10 product sales. The requirement is to display the percentage of each product’s sales across all products, including those that are not part of the top 10.

One approach to achieving this is by dividing each product’s sales by the overall sales (Product Sales/All Product Sales). Here’s an example of how this can be done:

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]

In the above example, we define a named set called “Top10” that represents the top 10 products based on the “Reseller Order Count” measure. We then create a calculated member called “PercentSold” that calculates the percentage of each product’s sales by dividing the “Reseller Order Count” measure by the total sales of all products. The “Format_String” property is set to “Percent” to display the result as a percentage.

Another approach is to calculate the percentage of each product’s sales divided by the sales of the top 10 products (Product Sales/Top 10 Product Sales). Here’s an example of how this can be achieved:

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]

In this example, we follow a similar approach as before, but instead of dividing by the overall sales, we divide by the sales of the top 10 products. The calculated member “PercentTopSold” calculates the percentage of each product’s sales by dividing the “Reseller Order Count” measure by the sum of the sales of the top 10 products.

By understanding these two approaches, you can effectively calculate and display percentages in MDX queries in SQL Server. It’s important to carefully consider the logic and requirements of your specific scenario to ensure accurate results.

Remember, MDX queries provide powerful capabilities for analyzing multidimensional data in SQL Server, and understanding how to calculate percentages is just one aspect of leveraging this functionality.