cancel
Showing results for
Did you mean:
Helper II

## GAP Report of Top 100 Sellers Not Sold

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.

3 REPLIES 3
Super User

Hi,

Share some data and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

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.

Super User

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

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Announcements

#### Launching new user group features

Learn how to create your own user groups today!