Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Average By Category with weight

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:

Datecategoryproductweightcount
January 2019CatAProduct115
January 2019CatAProduct253
January 2019CatBProduct3103
January 2019CatCProduct4110

 

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

DatecategoryproductweightcountAverage
January 2019CatAProduct1152,5
January 2019CatAProduct2532,5
January 2019CatBProduct31033,07
January 2019CatBProduct41103,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

 

 

1 ACCEPTED SOLUTION
v-cherch-msft
Employee
Employee

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]))

a1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-cherch-msft
Employee
Employee

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]))

a1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.