DAX Grouping and Ranking in Calculated Columns

Question

My raw data stops at sales - looking for some DAX help adding the last two as calculated columns.


  customer_id   order_id   order_date   sales   total_sales_by_customer   total_sales_customer_rank  
 ------------- ---------- ------------ ------- ------------------------- --------------------------- 
  BM                   1   9/2/2014       476                       550   1                          
  BM                   2   10/27/2016      25                       550   1                          
  BM                   3   9/30/2014       49                       550   1                          
  RA                   4   12/18/2017      47                       525   3                          
  RA                   5   9/7/2017       478                       525   3                          
  RS                   6   7/5/2015         5                         5   other                      
  JH                   7   5/12/2017        6                         6   other                      
  AG                   8   9/7/2015         7                         7   other                      
  SP                   9   5/19/2017       26                       546   2                          
  SP                  10   8/16/2015      520                       546   2

Wouldn't it make more sense to create these values in meassures rather than in calculated columns? — VBA Pete
I think it depends on the use case. For this, I'm more interested in understanding how dax would work from a column rather than a measure perspective. — Roman

Aldert Aldert · Accepted Answer · 2020-08-12T16:23:38

Lets start with total sales by customer:

total_sales_by_customer = 
var custID = orders[customer_id]
return CALCULATE(SUM(orders[sales], FILTER(orders, custID = orders[customer_id]))

first we get the custID, filter the orders table on this ID and sum it together per customer.

Next the ranking:

total_sales_customer_rank = 
var rankMe = RANKX(orders, orders[total_sales_by_customer],,,Dense)
return if (rankMe > 3, "other", CONVERT(rankMe, STRING))

We get the rank per cust sales (gotten from first column), if it is bigger than 3, replace by "other"

On your first question: DAX is not like a programming language. Each row is assessed individual. Lets go with your first row: your custID will be "BM". Next we calculate the sum of all the sales. We filter the whole table on the custID and sum this together. So in the filter we have actualty only 3 rows! This is repeated for each row, seems slow but I only told this so you can understand the result you are getting back. In reality there is clever logic to return data fast. What you want to do "Orders[Customer ID]=Orders[Customer ID]" is not possible because your Orders[Customer ID] is within the filter and will run with the rows..

var custid = VALUES(Orders[Customer ID]) Values is returning a single column table, you can not use this in a filter because you are then comparing a cell value with a table.

DAX Grouping and Ranking in Calculated Columns

1 Answers