Count customers with more than X in sales?

Share The Knowledge

My solution is based on function SUMMARIZECOLUMNS that creates a virtual table.

Requirement

How many customers have more than $10000 in total sales?

Files: Pbix & Excel

Final Measure

Count Customers with more than X in Sales = 
COUNTROWS(
  FILTER(
       SUMMARIZECOLUMNS(Customer[ID],
                        "CustomerSales", SUM(Sales[Sales])
       ),
       [CustomerSales] > 10000
  )
)

Set-up

Raw Data

Table Sales

Each row is a sale to a customer (ID) on a particular date.

Table Relationships

Table Sales is the many side (fact or transaction)

My DAX solution uses Tables Customer & Sales.

Solution

Concept

Sales table has repeating customer IDs with daily sales. We need to:

  1. aggregate all the sales for each customer
  2. filter out those with less than $10000
  3. count remaining customers

Step 1 Aggregate

SUMMARIZECOLUMNS(Customer[ID],
                 "CustomerSales", SUM(Sales[Sales]))

SUMMARIZECOLUMNS creates a summary table. It sums column Sales for each Customer ID. It gives us this:

How can we see the values above? Aren’t the values created virtually inside the measure? Yes, that’s true.

For demo purposes I created a table to see the values. Not a best practice as creating tables increases the file size.

On the ribbon select: Modeling / New Table (and use code below).

zAggregatedSalesbyCustomer = SUMMARIZECOLUMNS(Customer[ID],
                        "CustomerSales", SUM(Sales[Sales]))

Step 2 Filter

zAggregatedSalesbyCustomer2 = 
  FILTER(
       SUMMARIZECOLUMNS(Customer[ID],
                        "CustomerSales", SUM(Sales[Sales])
       ),
       [CustomerSales] > 10000)

Now only these customers qualify:

The 2 rows above are virtual and inside the measure. For demo purposes you can create a table using this DAX to see them:

zAggregatedSalesbyCustomer2 = 
  FILTER(
       SUMMARIZECOLUMNS(Customer[ID],
                        "CustomerSales", SUM(Sales[Sales])
       ),
       [CustomerSales] > 10000)

Step 3 count rows

Finally, add COUNTROWS function to end up with the final measure:

Count Customers with more than X in Sales = 
COUNTROWS(
  FILTER(
       SUMMARIZECOLUMNS(Customer[ID],
                        "CustomerSales", SUM(Sales[Sales])
       ),
       [CustomerSales] > 10000
  )
)

Bonus Question1: Who are they?

Who are the 2 customers with more than $10000 in sales?

Solution1 uses a calculated column and a filter (or a slicer).

Calculated column in Customer dimension table

Customer table is a dim table. If it’s a short table then adding this column to the data model might not increase the file size much.

However, I can also solve this using only a measure and a filter. We’ve already got measure ‘Total Sales’ so I use it in the viz and viz filter:

Viz on left, viz filter on right.

Bonus Question2: Variable X amount

Up until now we’ve used the $10000 amount limit as a constant. How can we vary this amount? I’ll create a parameter and use it in a measure.

Step 1: create a parameter

On the ribbon select: Modeling / New parameter. Fill it out like this:

Creating a new parameter

After pressing ‘OK’ several things happen:

  • a 1 column table called ‘Total Sales X’ is created. The DAX code used to create the table can be modified.
Total Sales X = GENERATESERIES(500, 20000, 500)
  • a measure to get/harvest the selected value is created.
Total Sales X Value = SELECTEDVALUE('Total Sales X'[Total Sales X], 1000)

Step 2: Use parameter value in measure

My new measure will calculate the same thing as measure ‘Total Sales’ but only for customers whose total sales amount is greater than the current parameter value.

Total Sales above X = 
if(SUM(Sales[Sales]) > 'Total Sales X'[Total Sales X Value], SUM(Sales[Sales]), BLANK())

Note: use of functions IF and BLANK. Customers that evaluate to blank will not appear in the visual.

Below, the slicer allows me to easily change the amount and the viz updates.

Slicer changes number of visible rows in visual

Summary

Now that I’m building things, opposed to only studying, I’m starting to feel more comfortable with DAX. I also get practice with basic visual functionality. Practicing is essential (SQLBI agrees).

DAX function links


Share The Knowledge

Leave a Reply

Your email address will not be published. Required fields are marked *