Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Hi,
Share some data and show the expected result.
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.
@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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |