Sales of Same Products to Other Customers

Level: Intermediate

I was helping someone on a forum recently and I thought the question and solution would make an interesting blog article. I have adapted the scenario to work with Adventure Works customer purchases, but this concept would work equally well across other entities, such as sales territories, calendar years etc.

Problem Definition

The requirement is to be able to select a single customer from a list of customers and see what products they have purchased.  Then the next things is to be able to compare sales of this same group of products across all customers while ignoring sales of all other products.  Stated another way, the report should show sales from all customers but only for the products purchased by the original selected customer.  Now there is a “cheats” solution to this problem using visual level filters – more on that later.  The purpose of this article is to show a DAX only solution.

First Attempt Doesn’t Work

For the first attempt at this problem, I wrote a couple of measures…

Total Sales = SUM(Sales[ExtendedAmount])
Total Sales of these Products to Other Customers Doesn't Work 
   = CALCULATE([Total Sales], ALL(Customers))

…and then placed them into two different tables as shown below.

When I click on a customer key on the left in the filter table, the table on the right is cross filtered and shows the total sales to that one customer.

Report Page 1

But a problem arises when I add the second measure.  The total sales for the first customer is still correct (2 below), but the other total (3 below) is not doing what I want.  It is showing all sales of all products instead of total sales for the same products purchased by the customer as shown below.

Report Page 2

So Why Doesn’t it Work?

To understand why this doesn’t work, you have to think about table filters.  In Report Page 1 above, there is a filter on the selected customer (12301) but there is no filter on any products.  The results table on the left is only showing sales for the products purchased by that customer because all products that return BLANK (ie no sales) are automatically hidden in the visual.  There is no filter on products, it is just that the products with no sales are hidden.

In Report Page 2 above, the new measure added removes the filter from the customers table.  Now there is no filter on the customer table or the products table.  As a result total sales for all customers and all products are returned to the results table on the right.

Cheats Solution

Ok, there is a cheats solution (non-DAX) to this problem.  All you have to do is select the results visual (1 below) and then go to the filters pane on the right and set a filter on [Total Sales] to filter out blanks (2 below).

There is nothing wrong with this cheats solution for this particular visual, but the solution starts to fall apart when you try to do this with a different visual.  Say for example you want to see total Product Category Sales as show below.  You can see below that there is still a filter on [Total Sales] is not Blank, but this filter stops working the way I need it to because the Product Key is no longer in the visual.

DAX Only Solution

The secret to solving this problem with a DAX only solution is to “detect” the list of product keys purchased by the selected customer and then pass this list of product keys to the second measure.  After using the correct measure, this is the result shown below.  Note I have added some other value adding columns to the results table to enhance the insights.

The DAX formula is as follows.

Notice above that I have used the VAR syntax.  This syntax is really starting to grow on me. I think it makes the formulas easier to read and understand – don’t you agree?  So the first thing I do is create a variable ProductList that is a single column table containing all the products in the filter context.  The secret sauce here is the black magic on line 3.  This is an advanced topic in its own right and I explain how it works in this article here.  In this scenario, line 3 looks at the Sales table in the current filter context (ie which products has this one selected customer purchased) and then pushes this filter to the Products table.  VALUES then produces a single column table of just these products purchased by the customer. This new virtual table created by VALUES retains lineage to the Products table, so any time this virtual VALUES table is used as a filter in a function, the filter will propagate as if it were part of the physical data model.

As you can see in the image below, it is possible to swap out the product key for product category with this DAX only solution unlike with the cheat’s approach above.

Use VALUES to Bring Back Filters

The above solution is actually harder than it needs to be (although as you can see it does work and it is a great learning example).  The easiest solution to this problem however is to use the VALUES function to “bring back” a filter that has been otherwise removed.  Take the following formula.

Test = CALCULATE([Total Sales], ALL(Customers))

The ALL portion of the above formula removes the filter from the Customers table.  This is required in order to be able to display the sales from other customers, but it will simply return all sales for all customers.  To solve the problem you then need to bring back a filter on just the products the selected customer purchased.  Where can you get such a list of products that the selected customer purchased?  from the Sales table, that’s where, using VALUES(Sales[ProductKey]).  When put together into the final formula, you get the following

VALUES Solution = 
          CALCULATE(
                    [Total Sales], 
                    ALL(Customers),
                    VALUES(Sales[ProductKey])
                   )

The ALL(Customers) portion removes the filter from the selected customer, then the VALUES(Sales[ProductKey]) reapplies a filter onto just the products that were purchased by the selected customer.

You can download a copy of the workbook I used for this article below.

Same Products to Other Customers