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
Go to 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])
View solution in original post
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.
thanks for the quick reply. here is the link:
Check out the on demand sessions that are available now!
Check out the Winners!
Features releasing from October 2020 through March 2021