cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kevinsray
Helper II
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
Ashish_Mathur
Super User
Super User

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-jingzhang
Community Support
Community Support

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
Super User
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...

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.