Slice on an element present in different fields

[Raph]

Hello,

Let's say I have products with different possible colors.

I would like to have a pivot table with one color that can be present in different fileds. In the exemple below, I would like to have Green on the row of my pivot table which will sum the sales of Product2, Product4 and Product6.

How do you suggest to proceed?

Thank you

Raphaël

[Raph]

Thank you very much !

[tamerj1 · Accepted solution]

Hi @Raph 
Please do the following

Create a filter table containing all colors 

Colors = 
SELECTCOLUMNS (
    FILTER (
        DISTINCT (
            UNION (
                VALUES ( Products[Color1] ),
                VALUES ( Products[Color2] ),
                VALUES ( Products[Color3] )
            )
        ),
        [Color1] <> BLANK ( ) 
    ),
    "Color", [Color1]
)

Then your measure would be

Number of Products = 
VAR SelectedColor = SELECTEDVALUE ( Colors[Color] )
RETURN
    COUNTROWS ( 
        FILTER ( 
            Products,
            Products[Color1] = SelectedColor
                || Products[Color2] = SelectedColor
                || Products[Color3] = SelectedColor
        )
    )