Summing the Prices of Common Products Across Different Categories

[powerbineeded]

My data consists of four columns: Supplier, Brand, Product, and Price. Each Supplier has multiple brands (with Brand being unique across all suppliers), and each Brand has multiple products. How can I display only the products that are common across the selected brands when I place Brand in the columns, Product in the rows, and Price in the values?

For example:

• If I select Brand X and Brand Y, the matrix should only show the products that are available in both Brand X and Brand Y.
• If I select Brand X and Brand Z, the matrix should only show the products that are available in both Brand X and Brand Z.
• If I select Brand X, Brand Y, and Brand Z, the matrix should only show the products that are available in all three brands.

In the picture, matrix should only show the 2 common products in red circle.

• 
•  

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[v-denglli-msft]

Hi @powerbineeded ,

Is my follow-up just to ask if the problem has been solved?
 johnt75's solution worked in my test.

If so, can you accept the correct answer as a solution or share your solution to help other members find it faster?

Thank you very much for your cooperation!

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75 · Accepted solution]

Try creating a measure like

Matches all chosen brands =
VAR NumVisibleBrands =
    CALCULATE (
        COUNTROWS ( VALUES ( 'Table'[Brand] ) ),
        ALLSELECTED ( 'Table'[Brand] ),
        VALUES ( 'Table'[Product] )
    )
VAR NumChosenBrands =
    COUNTROWS ( ALLSELECTED ( 'Table'[Brand] ) )
VAR Result =
    IF ( NumVisibleBrands = NumChosenBrands, 1 )
RETURN
    Result

Add this as a filter to the matrix visual and set it to only show when the value is 1.