About Calculating Sales depending on another Value in DAX

Author:Murphy | View: 28744 | Time: 2025-03-22 23:11:48

Our usual Measures aggregate the Values from columns in one Fact table. But what is needed to calculate a result that depends on Data in another Fact table? Let's look at a possible solution.

Introduction

What is needed to calculate a result based on a specific value in Dax?

Simple: A filter.

But what if the filter has to be a table?

Fortunately, I already know how to work with tables in DAX Measures:

On using intermediary results in DAX Measures

And now, my client gave me a challenge to put my knowledge to the test:

Look at the following Report:

Figure 1 – Online vs Retail Sales and sold products (Figure by the Author)

As you can see, the number of products sold differs between Online and Retail Sales.

My client asked the following questions:

What is the Online Sales Amount for the Products sold in Retail Stores as well?
And which Products are sold only Online?

Trying to solve it in Power BI with Filters

Can I answer the first question in Power Bi by applying a Filter on the left table like this?

Figure 2 – Applying a Filter in Power BI filtering by the Retail Sales Measure (Figure by the Author)

Unfortunately, this doesn't work.

The reason is that no Relationship connects these two tables, and the Filter direction to Dimension tables works only in one direction.

Yes, I could change it to bidirectional filtering. But this can introduce issues as it will not work in any situation.

Another way is to add multiple Measures and add a Filter to the Visual:

Figure 3 – Applying a filter to Power BI with multiple Measures (Figure by the Author)

This delivers the needed result.

I can even change the filter to "Sum Retail Sales is blank" to get the count of products sold only online together with the Sales.

But the resulting visual contains the Sum Retail Sales Measure, which I don't want to see. And, when I want to get the number of products sold only online, I will get empty columns for this measure, which is not what I want to see either.

So, I must create an explicit DAX Measure to solve the questions above.

Get the result from the source database

But first, I must get a reference result.

I can use this reference result to validate the results when I build the solution.

I write the following SQL query to get this reference result:

WITH [RetailProductsByMonth]
AS
  (SELECT DISTINCT [ProductKey]
            ,[D].[MonthKey]
    FROM [dbo].[FactSales] AS [F]
      INNER JOIN [Date] AS [D]
        ON [D].[Date] = [F].[DateKey]
  )
SELECT [D].[EnglishYearMonthShortName]
      ,[D].[MonthKey]
      ,COUNT(DISTINCT [F].[ProductKey]) AS [ProductCount]
  FROM [dbo].[FactOnlineSales] AS [F]
    INNER JOIN [Date] AS [D]
      ON [D].[Date] = [F].[DateKey]
  INNER JOIN [RetailProductsByMonth] AS [PbM]
      ON [PbM].[ProductKey] = [F].[ProductKey]
      AND [D].[MonthKey] = [PbM].[MonthKey]
  GROUP BY [D].[EnglishYearMonthShortName], [D].[MonthKey]
  ORDER BY [D].[MonthKey] DESC;

This is the result:

Figure 4 – Result of the SQL Query to get only the products sold Online and in Stores (Figure by the Author)

To get the count of products sold only Online, I use this SQL Query:

WITH [RetailProductsByMonth]
AS
  (SELECT DISTINCT [ProductKey]
            ,[D].[MonthKey]
      FROM [dbo].[FactSales] AS [F]
        INNER JOIN [Date] AS [D]
          ON [D].[Date] = [F].[DateKey]
  )
SELECT [D].[EnglishYearMonthShortName]
      ,[D].[MonthKey]
      ,COUNT(DISTINCT [F].[ProductKey]) AS [ProductCount]
  FROM [dbo].[FactOnlineSales] AS [F]
    INNER JOIN [Date] AS [D]
      ON [D].[Date] = [F].[DateKey]
    LEFT OUTER JOIN [RetailProductsByMonth] AS [PbM]
      ON [PbM].[ProductKey] = [F].[ProductKey]
      AND [D].[MonthKey] = [PbM].[MonthKey]
  WHERE [PbM].[ProductKey] IS NULL
  GROUP BY [D].[EnglishYearMonthShortName], [D].[MonthKey]
  ORDER BY [D].[MonthKey] DESC;

The result for this query is the following:

Figure 5 – Result of the SQL Query to get only the products sold Online (Figure by the Author)

In both cases, first, I get the list of all sold products for each month for the Retail Sales. Then, I join the result to the Online Sales table and count the monthly products sold.

I exclude all products in the Retail Sales Data in the second query.

In both cases, I get the needed reference result, and I can go ahead and write the DAX code in Power BI.

Querying the Data model

Instead of trying to build Measures in Power BI, I use DAX Studio to write DAX Queries to construct the solution step by step.

First, I want to get all products sold in Retail stores.

As my SQL query group the result per month, I restrict the first query to one month only. This simplifies the needed code as well:

DEFINE
  VAR MonthSelection = 202311

EVALUATE
  CALCULATETABLE(
          SUMMARIZECOLUMNS('Retail Sales'[ProductKey] )
          ,'Date'[MonthKey] = MonthSelection
          )

This is the result:

Figure 6 – Result of DAX Query with the list of Products sold in Retail Stores (Figure by the Author)

So, I got 2504 products sold during November 2023.

The next step is to inject this result as a filter into a query to count the products sold Online:

DEFINE
  VAR MonthSelection = 202311

  VAR RetailProductList =
        CALCULATETABLE(
                SUMMARIZECOLUMNS('Retail Sales'[ProductKey] )
                ,'Date'[MonthKey] = MonthSelection
                )

EVALUATE
{
  COUNTROWS(
        CALCULATETABLE(
                SUMMARIZECOLUMNS('Online Sales'[ProductKey] )
                ,'Online Sales'[ProductKey] IN RetailProductList ,'Date'[MonthKey] = MonthSelection
                )
        )
 }

Now I have 2498 products:

Figure 7 – Result of DAX Query counting Products sold Online and in Retail Stores (Figure by the Author)

As you can see, I store the list of products sold in Retail stores in the Variable RetailProductList.

Then, I use CALCULATETABLE() to filter the ProductKey in the Online Sales table with this variable.

To restrict the result to one month only, I must add line 17.

To get only the count of the products, I use COUNTROWS() enclosed in curly brackets to generate the output table. I must add the curly brackets, as COUNTROWS() returns a number, and EVALUATE expects a table expression. The curly brackets take the result of COUNTROWS() and create a table with one column and one row.

When I compare the count of 2498 to the result of the first SQL query above, for November 2023, I have the same result. Therefore, the logic works as expected.

Creating the Measure(s)

Now, I can take the query and put it into a Measure.

As the Measure must work even when I want to get a list of months and calculate the result for each month, I must remove the hardcoded month and evaluate the month for each row in the result:

DEFINE
 MEASURE 'All Measures'[Online Sold Product Count with Retail Sales] =
    VAR MonthSelection = MAX('Date'[MonthKey])

    VAR RetailProductList =
        CALCULATETABLE(
              SUMMARIZECOLUMNS('Retail Sales'[ProductKey] )
                                ,'Date'[MonthKey] = MonthSelection
                         )

RETURN
  COUNTROWS(
      CALCULATETABLE(
              SUMMARIZECOLUMNS('Online Sales'[ProductKey] )
                  ,KEEPFILTERS('Online Sales'[ProductKey] IN RetailProductList)
                  ,'Date'[MonthKey] = MonthSelection
                  )
              )
EVALUATE
  SUMMARIZECOLUMNS('Date'[Year Month Short Name]
            ,'Date'[MonthKey]
            ,"Online Sold Product Count with Retail Sales", [Online Sold Product Count with Retail Sales]
            )
 ORDER BY 'Date'[MonthKey] DESC

The Measure is everything between the DEFINE MEASURE and EVALUATE.

This is the result, which is the same as from the SQL query above:

Figure 8 – Result of DAX Query using the Measure derived from Query (Figure by the Author)

To get the products sold only Online, I must change one line in the Measure to exclude the products sold in Retail stores from the result:

,KEEPFILTERS(NOT ('Online Sales'[ProductKey] IN RetailProductList) )

The result is the same as from the second SQL Query above:

Figure 9 – Result of DAX Query using the Measure for Products sold only Online (Figure by the Author)

Conclusion

This task is similar to the logic described in my article about using intermediary results in DAX (You can find the link to that article in the Reference section below).

But this time, I didn't need the extra FILTER() when using the table variable.

I'm unsure why I needed it when I wrote that piece, but I will examine it.

As you have seen, I used DAX queries this time to develop the solution step-by-step.

I found it useful to do it this way, as I worked with an intermediary table, which I can examine during development.

For such scenarios, it is easier working with Queries than trying to build such a solution straight in Power BI Desktop.

Moreover, it's interesting for those who come from working with relational databases and who started working with DAX: I needed to explicitly add the month to the SQL query to be able to group the results by month. I don't need this in DAX, as the grouping is done automatically when adding the month to the query.

References

I wrote an article about working with intermediary tables in DAX Measures a few months ago. You can find it here:

On using intermediary results in DAX Measures

I use the Contoso sample dataset, like in my previous articles. You can download the ContosoRetailDW Dataset for free from Microsoft here.

I translated my client's request to the data in the ContosoRetailDW dataset.

The Contoso Data can be freely used under the MIT License, as described here.

I enlarged the dataset to make the DAX engine work harder. The Online Sales table contains 71 million rows (instead of 12.6 million rows), and the Retail Sales table contains 18.5 million rows (instead of 3.4 million rows).

Get an email whenever Salvatore Cagliari publishes.

I make my articles accessible to everyone, even though Medium has a paywall. This allows me to earn a little for each reader, but I turn it off so you can read my pieces without cost.

You can support my work, which I do during my free time, through

https://buymeacoffee.com/salvatorecagliari

Or scan this QR Code: