Weighed average with Filter on multiple columns

[Arpi]

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

[v-yuezhe-msft · Accepted solution]

@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

[TomMartens]

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

[Arpi]

HI.

thanks for the quick reply. here is the link:

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