Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Regular Visitor

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

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 =
    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



  • 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. 







Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors