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 ...
Need Help
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?