topic Re: Weighted average of the only the products that appear in ALL selected columns in DAX Commands and Tips

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

sunnycube — Thu, 15 May 2025 12:51:36 GMT

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?

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

johnt75 — Thu, 15 May 2025 13:48:00 GMT

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

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

Anonymous — Fri, 16 May 2025 05:37:07 GMT

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.