Solved

SSAS 2008 MDX Calculate Average Between Two Measures

Posted on 2010-09-10
1
1,568 Views
Last Modified: 2016-02-14
Hi,

I need to be able to calculate an Average over two seperate measures within a SSAS 2008 Cube.

In my cube, I have two measures, one called [Measures].[Order Quantity] and the other called [Measures].[Order Quantity Week On Week]. The later is a Calculated Measure.

I need to be able to find the Median Average between the two.

So, if [Measures].[Order Quantity] = 1000 and [Measures].[Order Quantity Week On Week] = 1200, I need the Calculation to return 1100.

Is it possible at all to Calulcate an Average over two measures in MDX ?

Thanks
0
Comment
Question by:Aspinall
1 Comment
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 250 total points
ID: 33651003
See   http://www.sqldev.org/sql-server-analysis-services/avg-function-using-calculated-measure-in-ssas-9390.shtml

As explained in the link above, in order for the average to be computed correctly and only include the count of the measure in the average when measure is not null or zero, another field (AdjCount) should be created to impute the correct denominator by placeing the following formula in a field called AdjCount.
        AdjCount = case isnull([Measures].[OrderQuantity], 0) when 0 then 0 else 1 end
then the computation of the Avg would be as follows:
       ( [Measures].[OrderQuantity] + [Measures].[Order Quantity Week On Week]) / AdjCount  
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now