Count number of top 10 products that each customer have bought

[Anonymous]

I'm trying to post again, since last time gave no reply. 

Hi all 

I want to know how many of the top 10 products (eg. 4 out of 10) that each customer have bought (E.g. if a customer bought 10.000 units of one top 10 product the meassure should return 1) 

I have calculated how many products (Modules) each customer have using the following meassure: 

CALCULATE(DISTINCTCOUNT('Module impressions (API)'[Module name]); GROUPBY('Module impressions (API)';'Module impressions (API)'[Customer ID]))

I have also ranked the products by number of times they have been sold (Impressions) using the following meassure: 

RANKX(ALLSELECTED('Modules All'[Module name]);CALCULATE(SUM('Module impressions (API)'[Impressions])))

I now want to calculate how many of the top 10 ranked products that each customer have. 

Currently my data looks like this: 

Customer database (Customers) 

Customer name	Info 1	Info 2	…	Info k
Customer A	…	…	…	…
Customer B	…	…	…	…
…	…	…	…	…
Customer i	…	…	…	…

Module database/ Product database (Modules All) 

Module name	Info 1	Info 2	…	Info k
Module A	…	…	…	…
Module B	…	…	…	…
…	…	…	…	…
Module j	…	…	…	…

Sales database / Impressions database (Module impressions (API)

Customer name	Module name	Impressions	Date	…	Info k
Customer A	Mudule A	639	01-01-2020	…	…
Customer A	Mudule A	216	02-01-2020	…	…
…	…	…	…	…	…
Customer B	Module B	945	01-01-2020	…	…
Customer B	Module B	107	02-01-2020	…	…
…	…	…	…	…	…
Custuomer i	Module j	X	X	…	…

 
And my visuals look like this 

Module name	Module rank	Impressions
Module A	1	83245575
Module B	2	74723660
…	…	…
Module j	Rank h	5371075

Customer name	# Modules
Customer A	35
Customer B	29
…	…
Custuomer i	1

I would like to add the # of top 10 modules/products to the table above in a new collumn. 

[Greg_Deckler]

Perhaps something along the lines of:

Measure = 
  VAR __Customer = MAX(Customers[Customer name])
  VAR __Top10 = SELECTCOLUMNS(FILTER('Module impressions',[Module Rank]<=10),"__Module",[Module name])
  VAR __Modules = SELECTCOLUMNS(FILTER('Module impressions',[Customer name]=__Customer,"__Module",[Module name])
RETURN
  10 - 
  COUNTROWS(
    EXCEPT(
      __Top10,
      __Modules
    )
  )

[Anonymous]

Hi @Greg_Deckler 

I tried putting in your dax code in a new meassure, but it cannot find the "[Customer name]" or the "[Module name]" in the variable ___modules, It seems that it can only find measures instead of tables or collumns. 

[Greg_Deckler]

Your table names are probably diffferent than what I used in my example code?

[Anonymous]

I tried using other table names, but i'm not even allowed to pick table names, only meassures.