Weighted average of the only the products that appear in ALL selected columns

[sunnycube]

I have a mock dataset that contains sales data from 4 different companies (OWN COMPANY, competitor 1-3). I already created a few measures, including the weighted average price:

Weighted AVG Price = SUMX('Example Data', 'Example Data'[Price]*'Example Data'[Units]) / SUM('Example Data'[Units])

Now I want to be able to apply a filter to it. For example, if I select only 2 competitors, I want the weighted average of only those products that are present in both of the selected competitors. Right now, the weighted average stays the same, no matter which competitor I select. I already tried a bunch of different stuff but it always stays unchanged.... Can someone please help?

[v-pagayam-msft]

Hi @sunnycube ,
Thank you @johnt75 for the helpful response!

I tried to recreate it with the sample data.Please find the file and screenshot.

I hope this resolve your query.If my answer meets your requirement,give us kudos and consider accepting it as solution.

Regards,
Pallavi.

[johnt75 · Accepted solution]

You can try

Weighted Average =
VAR Companies =
    CALCULATETABLE (
        VALUES ( 'Example Data'[Competitor] ),
        ALLSELECTED ( 'Example Data'[Competitor] )
    )
VAR NumCompanies =
    COUNTROWS ( Companies )
VAR CompaniesAndProducts =
    GENERATE ( Companies, CALCULATETABLE ( VALUES ( 'Example Data'[Product ID] ) ) )
VAR ProductsAndNumCompanies =
    GROUPBY (
        CompaniesAndProducts,
        'Example Data'[Product ID],
        "@num", SUMX ( CURRENTGROUP (), 1 )
    )
VAR ProductsInAll =
    FILTER ( ProductsAndNumCompanies, [@num] = NumCompanies )
VAR Result =
    CALCULATE (
        SUMX ( 'Example Data', 'Example Data'[Price] * 'Example Data'[Units] )
            / SUM ( 'Example Data'[Units] ),
        KEEPFILTERS ( ProductsInAll )
    )
RETURN
    Result