Forum Discussion

Arpi's avatar
Arpi
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 hav...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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