DAX – Comparing a specific Product Average against the average of ALL products

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 {(




                     )} 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.        https://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.        clip_image002[4]

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.      clip_image004[4]


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.      clip_image006[4]

                                                                                      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.        clip_image008[4]

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:




4 thoughts on “DAX – Comparing a specific Product Average against the average of ALL products

  1. Is it possible to make the Overall Product Average context sensitive? Going from your example, I’d like the Overall Product Average to be sensitive to major groupings such as Bike, or Clothing, without having to manually set those values.

    Attempts to change the ALL to ALLSELECTED leave me with the value returning the same as the average.

      • I worked with another person and we actually managed to stumble into a solution. Instead of All(), juse use allexcept([ProductCategory]) to produce rollup on a per category basis.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s