cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Weighed average with Filter on multiple columns

HI, I'd like to create a weighted average of analysis values based as follows:

each raw material has multiple samples (with an amount).

the samples are analyzed for different nutrients and they have their analyses results.

I would like to know what are the weighted average analysis values of a raw material 

This is the table (BMQCdata):

The BMArtCode is the raw material code,

the BMNutrCode is the nutrient code,

then there is the resultvalue and the Amount_kg.

In the last column (WeightAve, set up as measure) I would like to see what is the total weighted average of DM, Moist, CP of Corn

 

as far as I can tell, I need to (1) calculate the total amount of the different samples, per BMArtCode and BMNutrCode

(2) calculate the "Total analysis" =ResultValue*Amoun_kg for each row

(3)divide the Total analysis with the Total Amount to get the weighted averag(for each Article,Nutrient)

Thanks for the help

Powerbi table.JPG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

@Arpi ,

Create the following measure firstly.

TOTAL AMOUNT = CALCULATE(SUM(BMQCdata[Amount_kg]),ALLEXCEPT(BMQCdata,BMQCdata[BMArtCode],BMQCdata[BMNutrCode ]))


Secondly, create column below.

Total analysis = BMQCdata[ResultValue]*BMQCdata[Amount_kg]


At last, create the following measure .

weighted averag = DIVIDE(MAX(BMQCdata[Total analysis]),[TOTAL AMOUNT])


If you also require Total analysis to be measure, please create the following measures.

Total analysis1 = MAX(BMQCdata[ResultValue])*MAX(BMQCdata[Amount_kg])
weighted averag1 = DIVIDE([Total analysis1],[TOTAL AMOUNT])



Regards,
Lydia

Community Support Team _ Lydia Zhang
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

3 REPLIES 3
Highlighted
Super User II
Super User II

Hey,

 

please prepare a pbix or xlsx file with sample data that reflects your data, upload the file to onedrive or dropbox and share the link.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted

HI.

thanks for the quick reply. here is the link:

https://1drv.ms/u/s!ApO_gLL3KvZ8hZcBoAzX8v3DE2229w

 

Highlighted
Microsoft
Microsoft

@Arpi ,

Create the following measure firstly.

TOTAL AMOUNT = CALCULATE(SUM(BMQCdata[Amount_kg]),ALLEXCEPT(BMQCdata,BMQCdata[BMArtCode],BMQCdata[BMNutrCode ]))


Secondly, create column below.

Total analysis = BMQCdata[ResultValue]*BMQCdata[Amount_kg]


At last, create the following measure .

weighted averag = DIVIDE(MAX(BMQCdata[Total analysis]),[TOTAL AMOUNT])


If you also require Total analysis to be measure, please create the following measures.

Total analysis1 = MAX(BMQCdata[ResultValue])*MAX(BMQCdata[Amount_kg])
weighted averag1 = DIVIDE([Total analysis1],[TOTAL AMOUNT])



Regards,
Lydia

Community Support Team _ Lydia Zhang
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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors