By: Dallas Snider | Comments | Related: > Analysis Services Development
Problem
How can you calculate an average in SQL Server MDX? Can calculating an average in MDX be accomplished in multiple ways? Check out this tip to learn more.
Solution
The solution presented in this tip was created in SQL Server Management Studio. The AVG function in SQL Server MDX allows for the calculation of an average measure over a specified set of dimension values.
In the following code sample, I have added line numbers at the end of each line to help explain the syntax of this query.
- In lines 3 through 6, we are creating a member named AverageSalesPerDay that uses the SQL Server MDX Avg function to calculate the Average Internet Sales Per Day for each Ship Date. Each Ship Date is returned by the SQL Server MDX Descendants function.
- Lines 9 and 10 define the calculated member AverageAmountPerSale by dividing the Internet Sales Amount by the Internet Sales Count.
- Lines 12 through 21 are the select statement.
- Lines 14 and 15 are the measures (sometimes these are called facts) that we are selecting directly from the Analysis Services Tutorial cube. Please note that the measures are separated by a comma just like columns are separated by a comma in an SQL statement.
- Lines 16 and 17 are the measures from the calculated members defined in Lines 1 through 10.
- Line 19 specifies that we are slicing the measures by the Ship Date's Fiscal Quarter.
- Line 20 is used to select dates only from the 2008 fiscal year from the Ship Date role-playing dimension.
- Line 21 specifies that we are selecting from the Analysis Services Tutorial cube. Please note the closing parenthesis at the end of the line goes with the opening parenthesis on Line 20.
WITH //01
// using the SQL Server MDX Avg function //02
MEMBER Measures.[AverageSalesPerDay] AS //03
Avg(Descendants([Ship Date].[Fiscal Date].CurrentMember, //04
[Ship Date].[Fiscal Date].[Date]), //05
[Measures].[Internet Sales-Sales Amount]) //06
//07
// an average of two measures //08
MEMBER Measures.[AverageAmountPerSale] as //09
[Measures].[Internet Sales-Sales Amount]/[Measures].[Internet Sales Count] //10
//11
SELECT non empty //12
{ //13
[Measures].[Internet Sales-Sales Amount], //14
[Measures].[Internet Sales Count], //15
[Measures].[AverageSalesPerDay], //16
[Measures].[AverageAmountPerSale] //17
} ON COLUMNS, //18
non empty [Ship Date].[Fiscal Date].[Fiscal Quarter].ALLMEMBERS ON ROWS //19
from (SELECT ([Ship Date].[Fiscal Year].&[2008]) on columns //20
FROM [Analysis Services Tutorial]) //21
The output from the SQL Server MDX code above is shown below. As you can see in this image, we can see the measure values sliced by fiscal quarter for the 2008 fiscal year.
Changing line 19 to slice on the Ship Date's Fiscal Semester yields the output shown below. Notice how all of the averages have changed due to the changes in the cube measure values.
To validate the results of the calculations, I ran the following SQL Server MDX query and then copied and pasted the results into Excel and used the Excel Average function to ensure the results were correct.
select non empty
{
[Measures].[Internet Sales-Sales Amount],
[Measures].[Internet Sales Count]
} on columns,
non empty [Ship Date].[Fiscal Date].[Date] on rows
from (SELECT ([Ship Date].[Fiscal Year].&[2008]) on columns
from [Analysis Services Tutorial])
Next Steps
- Experiment with different measures and dimensions in the SQL Server MDX query.
- Read about a quick way to start learning SQL Server MDX.
- Learn how to use SQL Server MDX to calculate both values and percentages for Analysis Services.
- Bookmark the SQL Server Analysis Services Glossary for future reference.
About the author
Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
