Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! 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])
Regards,
Lydia
@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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |