New Member

7 years ago

Solved

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

Anonymous

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