Forum Discussion

youssef123's avatar
youssef123
Frequent Visitor
2 years ago
Solved

Comparing Customers' Purchased Products

Dear PowerBI Community,

 

I would greatly appreciate any help with this problem.

 

The relationships between my tables is illustrated below:

CustomerA & CustomerB -> Customers using SK Customer
Customers -> Sales using SK Customer

Sales -> Items using SK Item

* The Items table consists of the column "Brand"

 

 

In the report below, I compare any two Customers that the user selects from the slicers. The left side of the report is completely disconnected from the right side and vice versa. In the table below each graph, I want to illustrate the brands that the customer is not buying, but the other is. For example, if Customer 1 buys a Ferrari and Customer 2 doesn't, it will show in the table under Customer 2. Of course, this data can only be shown after the user selects the two customers who they wish to compare.

The Customer 1 slicer displays the Customer Name column from the CustomerA table as shown in the image above. The Customer 2 slicer displays the Customer Name column from the CustomerB table.


One of my approaches involved creating a measure to access the SELECTEDVALUE from CustomerA and CustomerB and then finding the Brands as required, however, when I came to apply it to the tables under Customer 1 and 2, I was forced to keep the interaction between the left and the right for the tables. This is because the measure is attempting to capture the selected customers 1 and 2. The problem with this is that the table always showed BLANK as the two slicers were more of less filtering on the same column, which is Customer Name.

I need a solution for this problem as nothing that I tried has worked so far. Thank you!

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi youssef123 ,

    You can create two measures as below to get it, please find the details in the attachment.

    Brands Customer YYY is buying but ZZZ is not = 
VAR _YYY =
    CALCULATE (
        MAX ( 'Customers'[SK Customer] ),
        FILTER ( 'Customers', 'Customers'[Customer Name] = "YYY" )
    )
VAR _ZZZ =
    CALCULATE (
        MAX ( 'Customers'[SK Customer] ),
        FILTER ( 'Customers', 'Customers'[Customer Name] = "ZZZ" )
    )
VAR _yskitems =
    CALCULATETABLE (
        VALUES ( 'Sales'[SK Item] ),
        FILTER ( 'Sales', 'Sales'[SK Customer] = _YYY )
    )
VAR _zskitems =
    CALCULATETABLE (
        VALUES ( 'Sales'[SK Item] ),
        FILTER ( 'Sales', 'Sales'[SK Customer] = _ZZZ )
    )
VAR _tab =
    SUMMARIZE (
        FILTER (
            'Items',
            'Items'[SK Item]
                IN _yskitems
                    && NOT ( 'Items'[SK Item] IN _zskitems )
        ),
        Items[Brand]
    )
RETURN
    CONCATENATEX ( _tab, 'Items'[Brand], "," )
    Brands Customer ZZZ is buying but YYY is not = 
VAR _YYY =
    CALCULATE (
        MAX ( 'Customers'[SK Customer] ),
        FILTER ( 'Customers', 'Customers'[Customer Name] = "YYY" )
    )
VAR _ZZZ =
    CALCULATE (
        MAX ( 'Customers'[SK Customer] ),
        FILTER ( 'Customers', 'Customers'[Customer Name] = "ZZZ" )
    )
VAR _yskitems =
    CALCULATETABLE (
        VALUES ( 'Sales'[SK Item] ),
        FILTER ( 'Sales', 'Sales'[SK Customer] = _YYY )
    )
VAR _zskitems =
    CALCULATETABLE (
        VALUES ( 'Sales'[SK Item] ),
        FILTER ( 'Sales', 'Sales'[SK Customer] = _ZZZ )
    )
VAR _tab =
    SUMMARIZE (
        FILTER (
            'Items',
            'Items'[SK Item]
                IN _zskitems
                    && NOT ( 'Items'[SK Item] IN _yskitems )
        ),
        Items[Brand]
    )
RETURN
    CONCATENATEX ( _tab, 'Items'[Brand], "," )

     However it is static, if your data refer many other customer comparison. It will need to create another measures to get them. Whether you are trying to get which brands are only purchased by specific customers? Could you please provide your final expected result with backend scenario and calculation logic? It would be helpful to find a suitable solution. Thank you.

    Best Regards

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    I am not sure how much i can help but i'd like to try.  Share data in a format that can be pasted in an MS Excel file and on that dummy dataset, show the expected result.

  • youssef123's avatar
    youssef123
    Frequent Visitor
    2 years ago

    Hello, thanks for your reply. Please see below for a sample dataset and the expected output for the tables.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi youssef123 ,

      You can create two measures as below to get it, please find the details in the attachment.

      Brands Customer YYY is buying but ZZZ is not = 
VAR _YYY =
    CALCULATE (
        MAX ( 'Customers'[SK Customer] ),
        FILTER ( 'Customers', 'Customers'[Customer Name] = "YYY" )
    )
VAR _ZZZ =
    CALCULATE (
        MAX ( 'Customers'[SK Customer] ),
        FILTER ( 'Customers', 'Customers'[Customer Name] = "ZZZ" )
    )
VAR _yskitems =
    CALCULATETABLE (
        VALUES ( 'Sales'[SK Item] ),
        FILTER ( 'Sales', 'Sales'[SK Customer] = _YYY )
    )
VAR _zskitems =
    CALCULATETABLE (
        VALUES ( 'Sales'[SK Item] ),
        FILTER ( 'Sales', 'Sales'[SK Customer] = _ZZZ )
    )
VAR _tab =
    SUMMARIZE (
        FILTER (
            'Items',
            'Items'[SK Item]
                IN _yskitems
                    && NOT ( 'Items'[SK Item] IN _zskitems )
        ),
        Items[Brand]
    )
RETURN
    CONCATENATEX ( _tab, 'Items'[Brand], "," )
      Brands Customer ZZZ is buying but YYY is not = 
VAR _YYY =
    CALCULATE (
        MAX ( 'Customers'[SK Customer] ),
        FILTER ( 'Customers', 'Customers'[Customer Name] = "YYY" )
    )
VAR _ZZZ =
    CALCULATE (
        MAX ( 'Customers'[SK Customer] ),
        FILTER ( 'Customers', 'Customers'[Customer Name] = "ZZZ" )
    )
VAR _yskitems =
    CALCULATETABLE (
        VALUES ( 'Sales'[SK Item] ),
        FILTER ( 'Sales', 'Sales'[SK Customer] = _YYY )
    )
VAR _zskitems =
    CALCULATETABLE (
        VALUES ( 'Sales'[SK Item] ),
        FILTER ( 'Sales', 'Sales'[SK Customer] = _ZZZ )
    )
VAR _tab =
    SUMMARIZE (
        FILTER (
            'Items',
            'Items'[SK Item]
                IN _zskitems
                    && NOT ( 'Items'[SK Item] IN _yskitems )
        ),
        Items[Brand]
    )
RETURN
    CONCATENATEX ( _tab, 'Items'[Brand], "," )

       However it is static, if your data refer many other customer comparison. It will need to create another measures to get them. Whether you are trying to get which brands are only purchased by specific customers? Could you please provide your final expected result with backend scenario and calculation logic? It would be helpful to find a suitable solution. Thank you.

      Best Regards