How to use RANKX to find the rows from a virtual table

Power BI
1

Hi,
I have been watching Enterprise DNA video “Cross Selling Analytics”.

Based on this example, I have been trying to add a step further by showing only the top 5 clients by sales who purchased both products. And this effort isn’t working so far. If someone could offer some guidance, I would highly appreciate it.
The huddle is how to apply RANKX to the result of INTERCEPT of customers instead of All customers, as shown below:

  1. The following snip of code can get the sales for customer who bought both products. This works.

Purchased Both Products =
VAR InitialPurchase = VALUES( Sales[Customer Name Index] )
VAR ComparisonPurchase =
CALCULATETABLE(
VALUES( Sales[Customer Name Index] ),
ALL( Products ),
TREATAS(
VALUES( ‘Comparison Products’[Index] ),
Sales[Product Description Index]
)
)

RETURN
CALCULATE( [Total Sales], INTERSECT( InitialPurchase, ComparisonPurchase ) )

  1. However, i would like to return on the sales for top 5 customers who bought both products. The following code didn’t go through:

Rank for Purchased Both Products =
VAR InitialPurchase = VALUES( Sales[Customer Name Index] )
VAR ComparisonPurchase =
CALCULATETABLE(
VALUES( Sales[Customer Name Index] ),
ALL( Products ),
TREATAS(
VALUES( ‘Comparison Products’[Index] ),
Sales[Product Description Index]
)
)

RETURN
CALCULATE( RANKX(ALL(INTERSECT( InitialPurchase, ComparisonPurchase )), [Total Sales], , DESC )
)

The error message is “ALL Function expects a table reference for argument 1, but a table expression was used”. The ALL function can’t be applied to the INTERSECT of customers who purchased both products and can only applied to all customer in the original customer table.

In summary, I got error when i tried to apply the ALL function to INTERSECT of customers (a virtual table) instead of all Customers. This is due to ALL can only be applied to Tables and not to Table Expressions.

So the question here is how to apply the RANKX to the result of INTERCEPT of customers instead of All customers, so that only the top 5 customers who bought both products will show.

Thank for all your help! Sharon

2

Utilize RANKX in Power BI to determine the ranking of rows within a virtual table. This function evaluates a specified expression and returns the rank based on the order specified.