Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

MDX Monday: Calculate Daily Average Sales

  • 20 July 2015
  • Author: DustinRyan
  • Number of views: 9766
  • 0 Comments

Creating an SSAS MDX calculations for daily average sales is a pretty common requirement I’ve come across as a consultant for Pragmatic Works and as the instructor for Pragmatic Works Introduction to MDX class. Because of this and the fact that many people often come to me with their MDX challenges, I’ve decided to start a series of blog posts covering these challenges and experiences both as a reference for myself and other and also as a way to give back to the community. These examples will be done using the Adventure Works cube unless otherwise stated. Some of these will be simple and some will be more complicated but I hope you will enjoy learning a little bit about MDX as much I do so lets begin!

There are two basic parts to creating this calculation. The first step is we need to create a measure to count the days of whatever period we happen to be browsing. To do this we can use the Count function and the Descendants function.

with member [Measures].[Day Count] as 

    Count(
        Descendants(
            [Date].[Calendar].currentmember,
            [Date].[Calendar].[Date]
            )
        )

Select {[Measures].[Day Count]} on 0,

NON EMPTY
[Date].[Calendar].[Month].members on 1

From [Adventure Works]

 

image

Now that the calculation for the day count is working, all we have to do is create an additional calculation to divide the Internet Sales Amount measure by the Day Count measure.

with member [Measures].[Day Count] as 

    Count(
        Descendants(
            [Date].[Calendar].currentmember,
            [Date].[Calendar].[Date]
            )
        )

member [Measures].[Average Daily Sales] as

[Measures].[Internet Sales Amount]/[Measures].[Day Count]

Select {[Measures].[Day Count],
    [Measures].[Average Daily Sales]} on 0,

NON EMPTY
[Date].[Calendar].[Month].members on 1

From [Adventure Works]

 

image

And there we have it. You may be wondering about why I didn’t use the Average function. The Average function only calculates the average over non-empty cells but for our purposes we need to consider days when a sale did not occur so the Average function won’t work in this situation.

More MDX Resources

If you’re looking for some other helpful resources related to MDX here’s some suggestions:

SQLDusty.com/category/mdx/ – Duh! ;)
MDXpert.com – Great resource for information on most of the MDX functions.
Chris Webb’s blog – Chris Webb has a lot of great MDX info on his site. Definitely worth the visit.

Got Feedback?

If you have any feedback or questions regarding this, please leave a comment down below or send me a message on Twitter!


Print
Categories: Miscellaneous
Tags:
Rate this article:
No rating
DustinRyan

DustinRyanDustinRyan

Other posts by DustinRyan

Please login or register to post comments.