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.
Hello! I am trying to build this report where I have to identify customers who have NOT bought a particular product but has bought other products.
I have attached an example spreadsheet which Customer, Item and Sales tables. Objective is to dynamically select an item from a slicer and the resulting table should give Customers who have NOT bought that item with a Total Sales Amount (from other products) in descending order.
Solved! Go to Solution.
Hi @gng,
If I understand you correctly, you should be able to use the formula below to create a new measure, then show it on a Table visual with Sales[Customer ID] column on the report to get your expected result.
Measure = IF ( HASONEVALUE ( Items[Item] ), CALCULATE ( SUM ( Sales[Sales] ), FILTER ( Sales, NOT ( CONTAINS ( Sales, Sales[Item], VALUES ( Items[Item] ) ) ) ) ), SUM ( Sales[Sales] ) )
Note: make sure there is no any relationship between the Item table and Sales table.
Here is the sample pbix file for your reference.
Regards
Has anyone found the solution to the Multiple Products question posed above?
Hi @gng,
If I understand you correctly, you should be able to use the formula below to create a new measure, then show it on a Table visual with Sales[Customer ID] column on the report to get your expected result.
Measure = IF ( HASONEVALUE ( Items[Item] ), CALCULATE ( SUM ( Sales[Sales] ), FILTER ( Sales, NOT ( CONTAINS ( Sales, Sales[Item], VALUES ( Items[Item] ) ) ) ) ), SUM ( Sales[Sales] ) )
Note: make sure there is no any relationship between the Item table and Sales table.
Here is the sample pbix file for your reference.
Regards
Hi,
Very nice solution. How can adapt it if i want to see customers who did not buy more than one specific product?
Because with this solution, when i select more than one product, It doesn't work
Hello @v-ljerr-msft ,
How would I include a date slicer, which when selected, customers who didin't buy the selected product within that period showed up in the list?
Thank you!
Hi @v-ljerr-msft,
With your help, I am able to get Sales Amount for a customer who didnt order a specific product. Is there a way to calculate Sales for specific product(s) for a customer who didnt order another specific product?
In this example, show only those customers who bought either "x" or "w" but not "v".
Thanks for your time and help!
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 |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |