Average By Category with weight

[Anonymous]

Hi All,

I need your help.

I'm developping a matrix report and I need to create a measure for the average of a Category of Product.

I have a table like this:

Date	category	product	weight	count
January 2019	CatA	Product1	1	5
January 2019	CatA	Product2	5	3
January 2019	CatB	Product3	10	3
January 2019	CatC	Product4	1	10

And I need to have a report to display the Average of the Category like this:

Date	category	product	weight	count	Average
January 2019	CatA	Product1	1	5	2,5
January 2019	CatA	Product2	5	3	2,5
January 2019	CatB	Product3	10	3	3,07
January 2019	CatB	Product4	1	10	3,07

Please Can you help us to create this Average Measure ?

The formula is : Sum (weight * Count By Category ) / Sum (Count By Category)

Thank you for your help

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may try below measure:

Measure = SUMX(FILTER(ALL(Table1),Table1[category]=MAX(Table1[category])),Table1[weight]*Table1[count])/CALCULATE(SUM(Table1[count]),ALLEXCEPT(Table1,Table1[category]))

Regards,

[Anonymous]

Hi @Anonymous  ,

 I think this should help:

MEASURE = CALCULATE( Sum (weight * Count ) / Sum (Count) , ALLEXCEPT( TableName , Category ) )

Note: If you have multiple Months in this table I would probably add MONTH as well within the ALLEXEPT function.

Let me know if that helps.

Cheers,
Robin

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