DAX for Sales of Customers who did not buy the selected product but having sales for other products.

[jrajeshcse]

Hi,

Can you please help with DAX for below report.  Need to report Sales of Customers who did not buy the selected product but having sales for other products.

Let's assume the tables are Customer(CustomerID,Fullname) and Product(ProductID,ProductName) and Sales (CustomerID, ProductID, Date, SaleAmount).  Customers are C1, C2, C3,C4,C5 and Products are P1,P2,P3.
C1 bought P1 &P2

C2 bought P1

C3 bought P2 & P3

C4 bought P3

C5 bought P1 & P3

REQUIRED:  When User select "P1" in the Slicer, Report should only show Customers C3 & C4 sales.

 
Tried below

= CALCULATE(SUM(SALES[SaleAmount),EXCEPT(ALL(Product[ProductName]),VALUES(Product[ProductName]))
But it is returning sales of all customers excluding C1 sales. No filter on Customers.

Thanks.

[v-ljerr-msft]

Hi @jrajeshcse,

In addition, you should also be able to use the formula below to create a new measure to indicate if a customer buys the selected product or not, then use it as a visual level filter on your visuals in your scenario. 

Measure = 
IF (
    NOT ( HASONEVALUE ( 'Product'[ProductID] ) )
        || NOT ( CONTAINS ( Sales, Sales[ProductID], MAX ( 'Product'[ProductID] ) ) ),
    1,
    0
)

Note: make sure there is no relationship between the Product table and Sales table. Here is the sample pbix file for your reference. 

Regards

[jrajeshcse]

Thanks for your reply. Above solution may not work as the customer can have negative sales. So the sum of sales indicate if the customer bought the product or not. Let me know your thoughts on this.

I tried below DAX.

Total Net Sales =
VAR vAction = IF(HASONEVALUE(CustomerAction[Action]),VALUES(CustomerAction[ActionID]),1)   // 1. Buy  2. Did not buy

VAR vNetSales = IF(vAction = 1,

//CALCULATE(
SUM(Sales[NetSalesAmount]), 
 //FILTER(SUMMARIZE(Customer, Customer[CustomerID], "TotalSales", SUM(Sales[NetSalesAmount])), [TotalSales] > 0), 
 
CALCULATE(SUM(Sales[NetSalesAmount]),
 FILTER(SUMMARIZE(Customer, Customer[CustomerID], "TotalSales", SUM(Sales[NetSalesAmount])), [TotalSales] <= 0),
 ALL('Product'[ProductShortDescription]))

RETURN vNetSales

[erik_tarnvik]

I have a solution that may not be ideal but may at least be a good starting point.

Create a table that represents the inverse relationship between products. With your sample data, the table DNB (for "did not buy") would look like this:

Name          PID
Product 1     P2
Product 1     P3
Product 2     P1
Product 2     P3
Product 3     P1
Product 3     P2

Create a one to many relationship between Product and DNB and set "cross filter direction" to "both". Use DNB[Name] for your slicer and you will get the desired behavior.

Now, I am sure there is a way to create the DNB table dynamically instead of manually. But it is late and I am tired . I'll think about it.

[erik_tarnvik]

I overcame my tiredness somehow... Here is how to create the DNB table dynamically so you can rely on Power BI to do it for you.

Go to the Data view and select "new Table" in the Modeling menu. Enter the following DAX expression:

DNB = FILTER(GENERATE(SELECTCOLUMNS('Product', "PPPID", [PID], "Product Name", 'Product'[Product Name]), 
                      SELECTCOLUMNS('Product', "PPID", [PID])),
             [PPID] <> [PPPID])

(Not sure if this is the most elegant way to achieve this but it will have to do)

Then go to the Relationships view and establish the relationship between DNB[PPID] and Products[PID] with cross filter direction set to "both" just as in the previous example. Again, Use DNB[Product Name] for your slicer and you will get the desired behavior. With the DNB calculated table, each time you refresh your data the DNB table will be recreated and serve it's purpose in your slicer.

[andystrev]

---

wrote:

I overcame my tiredness somehow... Here is how to create the DNB table dynamically so you can rely on Power BI to do it for you.

Go to the Data view and select "new Table" in the Modeling menu. Enter the following DAX expression:

DNB = FILTER(GENERATE(SELECTCOLUMNS('Product', "PPPID", [PID], "Product Name", 'Product'[Product Name]), 
                      SELECTCOLUMNS('Product', "PPID", [PID])),
             [PPID] <> [PPPID])

(Not sure if this is the most elegant way to achieve this but it will have to do)

Then go to the Relationships view and establish the relationship between DNB[PPID] and Products[PID] with cross filter direction set to "both" just as in the previous example. Again, Use DNB[Product Name] for your slicer and you will get the desired behavior. With the DNB calculated table, each time you refresh your data the DNB table will be recreated and serve it's purpose in your slicer.

---

HI,  could you expand on this a little more on how you setup the tables and the layout? I think this will help with my issue but i cant figure out how to set it up?

[jrajeshcse]

Hi,

I need help with the below report.  Need to report sales of customers who did not buy the selected product (Slicer in the report) but having sales for other products.

Available tables are Customer (CustomerID, CustomerName) ,  Product(ProductID, ProductName), Sales (CustomerID, ProductID, Date, SalesAmount).

Consider customers are C1, C2, C3,C4,C5 and Products are P1, P2,P3. 

C1 bought proucts P1 & P2. 
C2 bought P1 & P4.

C3 bought P2

C4 bought P2 & P3.

C5 bought P1
Report:
 When user selects "P1", My report should  only show C3 and C4 sales.

Thanks