Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

RankX DAX Help

Hello,

 

Trying to get RANKX to rank a list of results. The relevent tables are structured like this: one customer table is related to a sales documents table. The relationship is one to many from the customer table to the sales documents table. The sales documents table is filtered by date and the customer table is filtered by salesperson using slicers in the report context.

 

The listed values are "KG" in the sales documents table. I have 10 resulting customers for a particular time range. However, I have 15 customers in the customer table. This is my syntax:

 

Sale KG Rank = RANKX(ALLSELECTED(Customer), CALCULATE(sum('Sales Documents'[KG])))
 
This formula works well except it also shows customers who have blank KG (the ones with no sales in the selected time period). This means that when I sort the measure column in descending sequence, it shows the 5 customers without any sales, ranked last in a tie.
 
How do I filter the customer table to only result in a rank number when there are sales in the current filter context?
 
Thanks
Michael
Need Help

1 Reply