Calculating the weighted average of terms

[thanhphongle]

Hello community,

this is my database

Order ID	Product	Waiting time in months
1	iPhone 6	36
2	iPhone 6+	48
3	Samsung Galaxy S6	72
4	Samsung Note 4	24
5	iPhone 7	60
6	iPhone 7+	24

I want to calculate the weighted average of the waiting time in a barchart.

My first appendage was.

I didnt pick any dimension as I want to the weighted avg of all waiting time. My expression is.

sum((sum([Waiting time in months]/sum(TOTAL [Waiting time in months]))*[Waiting time in months])

This is not really working because I was using a multiple functions.

So I tried in the second step to add the Order ID as dimension and used the following expession.

sum([Waiting time in months]/sum(TOTAL [Waiting time in months]))*[Waiting time in months]

The result is, that qv  returns me the percentage of the weighted avg waiting time for each order ID.

But I need the sum of all calculated avg weights.

The result shoud be 51.27 !

Could anyone help me out here?

[tresesco]

Do you wish to see same avg value 51.27 across all Order IDs?

[tresesco]

If so try:

Sum(total Aggr(Sum([Waiting time in months])/Sum(TOTAL [Waiting time in months])*[Waiting time in months], [Order ID]))

[sunny_talwar]

How are you even coming up with 51.27 here? Can you explain the calculation here?

[thanhphongle]

No I just want to have one bar and actually I dont want to pick any Dimension for this.

You can see my calculation in the attached Excel File.

[tresesco · Accepted solution]

PFA (Used the same expression suggested above)

Edit: In fact 'Total' before aggr() would not be required without a dimension.

[sunny_talwar]

How about just this:

Sum(Aggr(([Waiting time in months]/Sum(TOTAL [Waiting time in months]))*[Waiting time in months]), [Order ID]))

Sum(Aggr(([Waiting time in months]/Sum(TOTAL [Waiting time in months])*[Waiting time in months]), [Order ID]))

UPDATE: Misplaced some parenthesis

[thanhphongle]

Thank you for your help guys !!!