Forum Discussion

alalei's avatar
alalei
Frequent Visitor
3 years ago

Need help - Matrix Visual

Hi community,

 

I have a table with customer ID, product code, and product group data. The combination of customer ID and Product Code in each row is unique. The table looks like this:

 

I want to create a matrix visual to show the number of customers for each pair of group I product and group II product. The desired table looks like this:
Before expand

After expand

 

Can anyone help me achieve this please?

Need Help

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi, I am not sure if I understood your question correctly, but please check the below.

    For now, the total shows 6, but if you have different business logic please describe what number needs to be shown as a total.

     

     

    expected result measure: = 
VAR _codelist =
    VALUES ( 'Product'[Product code] )
VAR _brandlist =
    VALUES ( 'Product'[Product brand] )
VAR _customerID =
    SUMMARIZE (
        FILTER ( Data, Data[Product Code] IN _codelist ),
        Data[Customer ID]
    )
VAR _result =
    COUNTROWS (
        FILTER (
            Data,
            Data[Customer ID]
                IN _customerID
                    && Data[Product Code] IN _brandlist
        )
    )
RETURN
    _result
    • alalei's avatar
      alalei
      Frequent Visitor
      3 years ago

      Hi Jihwan_Kim , thank you for your solution! I'm envisioning the total to be 9 (3+3+2+1) as:
      customer 2: ac, bc

      customer 3: ad

      customer 4: ac, ad

      customer 5: ac, ad, bc, bd
      Also, I have another table visual showing the customer ID, Product Code in Data table. When I click the row in the matrix, I want my table visual to be updated based on my selection. For example, when I click 3 on the second row of my matrix (i.e., ac pair), I want to see the three records in the table visual.

      Could you please provide any guidance on this?