cancel
Showing results for
Did you mean:
Highlighted
New Member

## 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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Highlighted
Super User II

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted
New Member

HI.

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

Highlighted
Microsoft

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors