Show only not sell products

[azaterol]

Hey all,

these matrix tables are showing the sales which customer is buying. E.g. Anna is buying Smirnof, Wodka, Coca Cola and so on.

Bill is buying Jack Daniels and so on. 

What I want to show is, only all these products which the customer didin't buy. Of course with no sale, because he never bought this product. 

Here in the Bill Matrix should display: Smirnoff, Wodka, Coca Cola, Coca Cola Light, Coca Cola Zero, Redbull, Bounty.

In the Anna Matrix should display: Jack Daniels, Fanta, Snickers

In the Fred Matrix sould display:  Smirnoff, Wodka, Coca Cola, Coca Cola Light, Coca Cola Zero, Redbull, Bounty, Jack Daniels, Fanta, Snickers.

Hope you understand me.

@amitchandak  was so kind, he help me out with an post before, but I got not Power Bi example.

So here is the power Bi example, which I upload in onedrive for you, to understand me better. 

https://1drv.ms/u/s!AhxzpjNK0L10gR3oL5hREjxrB7FN?e=3uepXU

Thank you all for help. 

[azaterol]

Hey everybody,

is there a measure to show only the products which the customer not bought?

Like here in excel I have a simple example.

The big problem is, that only if a customer buys a products it shows which products he bought, when he bought and the product number.

Have you an idea how I can create a measure to show the not bought products ?

Thank you all for help

[amitchandak]

@azaterol , posted file on

https://community.powerbi.com/t5/Desktop/Show-only-not-sell-products/m-p/2634491#M925684

[amitchandak]

@azaterol , Make sure you have two dimension customer and product (separate table)

assume you have fact sales

measures  =

sales = sum(sales[sales Amount)

not purchased =

Countx(Filter( summarize(Sales, Customer[ID], Product[ID]) , isblank([sales]) ), [ID])

refer if needed

isblank is not just to check blank values, It can be used to flip the behavior of a measure, which comes handy to solve the problem like customer retention easily: https://www.youtube.com/watch?v=roGE2qrp-eA&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=54

[azaterol]

@amitchandak , in your new List it shows all of these products which Anna bought and in the wrong Group. E.g. she bought Bounty(left matrix), it shows also up in right site under Alcohol free.

Right matrix should only Fanta, Jack Daniels & Snickers.

[v-easonf-msft · Accepted solution]

Hi, @azaterol 

Add another table 'Seperate Customers Table' without any relationship:

Seperate Customers Table = Customers

Then you can create a custom filter measure and drag this measure to the matrix visual filter pane to filter data. 

filter = IF(SELECTEDVALUE(AuPox[CustomerID]) in VALUES('Seperate Customers Table'[Customer ID]),1,0) 

Best Regards,
Community Support Team _ Eason

[azaterol]

@v-easonf-msft  Thank you so much! It works perfectly! Par excelence! 

[amitchandak]

@azaterol , Check if the attached file can help