I had a situation when one of my fellow workers wanted to create a report where they wanted to compare one product’s sales and how it was doing when compared to the average of ALL the product sales.

In our example below we want to extract from the AdventureWorksDW2012 Analysis Services Database

You can get a copy of this here: http://msftdbprodsamples.codeplex.com/releases/view/55330

       ·         I used the AdventureWorksDW2012 Data File and AdventureWorks Multidimensional Models SQL Server 2012

Getting data from the OLAP Cube into the Tabular model

The first thing that I did was to get my data from the OLAP cube into the tabular model.

1.       Below is the query that I used to get the data.

Selectnonempty {[Measures].[Sales Amount]} on 0

,nonempty {(

                                           [Product].[Product].children

                                         ,[Date].[Date].Children

                     )} on 1

from [Adventure Works]

a.        As you can see from above this is a nice and simple MDX query.

2.       Next to get the data into the Tabular model you can refer to this following blog post that I did and follow steps under SSAS Tabular and then complete steps 1 – 3

a.        http://gqbi.wordpress.com/2013/12/11/power-view-using-mdx-ssas-tabular-and-power-view-to-display-potential-issues-based-on-past-trends-in-data/

3.       Now you will have your data in your Tabular Model.

4.       As shown below we just changed the column names:

a.       

b.       We then also changed the data type for the Date column to date

c.        And then changed the Data Format to Short Date

                                                                                       i.     

Creating the measures so that we can get the Product Average and Overall Product average

As per our example we wanted to compare a specific Products average against the average of all the products combined. And below we will show you how we created this.

NOTE: The thing to note here is that with the measures below you can ONLY use the Products column to slice on in your report.

·         This is because if you use another column your measure for Overall Product Average below are looking ONLY at the products column so the overall average will be incorrect.

1.       First we created the Product average measure which is as follows:

Product Average:=AVERAGE(‘Sales Amounts'[MeasuresSales Amount])

2.       Next this is where we created our Overall Product Average measure:

Overall Product Average:=CALCULATE(AVERAGE(‘Sales Amounts'[MeasuresSales Amount]);ALL(‘Sales Amounts'[Product]))

a.        The above measure can be explained with the following:

                                                                                       i.      CALCULATE

1.       This function is used to evaluate the expressions contained within the context.

                                                                                     ii.      AVERAGE(‘Sales Amounts'[MeasuresSales Amount])

1.       This is as per our first measure is used to calculate the average

                                                                                    iii.      ;ALL(‘Sales Amounts'[Product]))

1.       The ALL DAX function returns all the data in your table or column.

2.       So in our example we are getting ALL the data in our Products column.

b.       NOTE: The first thing to note here is that when using the ALL DAX function if you do not put the fully qualified column name when trying to create this measure you will get the following error as shown below:

                                                                                       i.     

                                                                                      ii.      So make sure you always use the Table name as well as the column name in all your measures. This can save you a lot of time trying to troubleshoot an error, when it was just a fully qualified column name.

3.       Now you will see the 2 measures below:

a.       

b.       NOTE: As it looks above they look identical, but this is because currently there are no Products to compare.

4.       Now when we put the above into Excel so that we can compare specific products you can see how the products have performed when comparing them to the overall product average: