Show a list of "Not-Sold Critical Itens" per client

[Lucas_Galanti]

I am developing a visual that shows what additional products from my portfolio I should offer to each client.

The reasoning behind it is something like this:

• I choose a channel/segment of clients (columns present in my dimension table dim_client)
• I list all the materials sold for this clients
• I check what products are bought by more than a specified X% of the total clients (this itens are now called "SUPERMIX")
• After that, I check every client that are part of this channel/segment, and list what itens from the Supermix they are NOT buying.
  
  

So far how I've done (Refer to the image below):

1. I put a filter for channel and segment acting in the page, as well as a date of sales 
2. I created the following measures: 
   ---------------------------------------------------------------------------------------
   N_Clients_Buy = DISTINCTCOUNTNOBLANK(fat_Sales[Client]))
   ----------------------------------------------------------------------------------------
   Client_Buys_N_Itens = DISTINCTCOUNT(fat_Sales[Material])
   ----------------------------------------------------------------------------------------
   N_Clients_Percentage_Of_Total =
   //here we check the % of clients that buy each item
   VAR _all_clients =

   CALCULATE(

       [N_Clients_Buy],

       ALLSELECTED(dim_Products)

   )

   RETURN DIVIDE([N_Clients_Buy], _all_clients)
   --------------------------------------------------------------------------------------------

Trigger_Perc_Client =

//Here we check if the percentage of clients that buy the item is greater than our trigger

VAR _trigger_select = Trigger_0_100[Trigger_0_100_Valor]

RETURN IF([N_Clients_Percentage_Of_Total]>=_trigger_select,1,0)

----------------------------------------------------------------------------------------------------

Trigger_Perc_Client_ALL =

//basically we just put a "ALLSELECTED" to be able to use this measure=1 as filter in the visual

CALCULATE([Trigger_Perc_Cliente],ALLSELECTED(dim_Clientes))

        ------------------------------------------------------------------------------------------------

• Now I create the left table, with the PK_Material column, the [N_clients_Buy] and the [N_Clients_Percentage_Of_Total] measure columns. The "Star" appears based on the result of the [Trigger_Perc_Client]. In this example, every item that is bought by more than 50% of the clients is flagged.

So far so good! I created my "SuperMix" porfolio 😀

• The right table is basically a matrix with "PK_Client" and "PK_Material" and the [Client_Buys_N_Itens] measure. By using the [Trigger_Perc_Client_ALL] = 1 as a filter, I see only the "supermix" products, which is great. So I can see which supermix products each client already buy.
  

But finally, what I really want is to see what supermix product each client DOES NOT buy. I thought that by selecting the "Show itens with no data" in the visual would show all the itens for all the custmores, but with a "N_Itens" =0 for the non-sold itens. But whenever I try it the program starts running forever and never finishes.