GAP Report of Top 100 Sellers Not Sold

[kevinsray]

Hi,
I am struggling to figure out how I can create a GAP report.
I have a query which pulls the top 100 products purchased, and have created a matrix visual which shows how many of each of those products was sold to each customer over the last 6 months.

Now I need to build a GAP report which shows the products in the top 100, which the customer has NOT purchased.

I am now bald as I have pulled out all my hair trying to figure out how to do this.

Any advice or suggestions would be greatly appreciated.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[v-jingzhang]

Hi @kevinsray 

I think you need to have a disconnected table which includes all customer values in a column. Then add a table visual (or any visual which can display multiple values of a field) and put customer column from previous table into it. In your original matrix visual, the customers who have NOT purchased a product are those whose number of product sold for a product is blank or zero. So you can create a measure to count the number of product sold for each customer. Use this measure as a visual-level filter on the new table visual and set show items when value is blank or zero. When you select a product from original matrix visual or a slicer, you will see which customers don't buy this product. If you want to show for each customer, he doesn't buy which products, logic is similar. 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[amitchandak]

@kevinsray , Assume you have Filter for [Top 100 Product]

Meausre  =

var _top = TOPN(100,allselected(Table[Product]),[last 6 month sales],DESC) //like this

var _sold = calculate(sum(Table[Value]), filter(Table, Table[product] in _top))

return

if(isblank(_sold), 1 , 0)

plot this againt the customer 

to get ciunt , use the values method suggested in blog

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...