I want to provide my users with a field "Price" that is calculated by dividing the "Billed Amount" by the "Billed Volume".
If aggregated it should not be a normal average but a weighted average. So for example this could be an overview in Query Studio:
Customer Billed Amount Billed Volume Price
Customer A 100 20 5
Customer B 200 20 10
Customer C 300 60 5
If the user removes the "Customer" column it should display the following:
Billed Amount Billed Volume Price
600 100 6
because (5*20 + 10*20 + 5*60)/100 = 6
The price should not be 20/3 = 6,6666666666666666666666666666667, because that would be the mathematical average.
I'm planning on achieving this by creating the following data item to calculate the price:
total(Billed Amount)/total(Billed Volume)
The aggregation of this field should be set to calculated.
This way I force to first aggregate the Billed Amount and the Billed Volume and then divide it.
Am I doing this right? Is this then indeed the correct way to get a weighted average? Or is this just working out in this simple example?
I think you are doing it right.
Weighted average could be also calculated this way (Billed Volume * Price) / Sum(Billed Volume) :
Customer Billed Amount Billed Volume Price Weighted Av
Customer A 100 20 5 1
Customer B 200 20 10 2
Customer C 300 60 5 3
Wich give a total of 6 for the 3 customers
And of course the most important question is:
QuoteI'm planning on achieving this by creating the following data item to calculate the price:
total(Billed Amount)/total(Billed Volume)
The aggregation of this field should be set to calculated.
Is that going to work? I think so!
Today we found out that the user has some additional requirements. The data we are working with is monthly data. If the user selects hour or day level they want to see the monthly value. If they select year level the value should be volume weighted averaged and displayed.
I think either the first one is possible or the second one, but not both in one model!
So I proposed to the user that we will break down the monthly values to hourly values and next do the volume weighted average aggregation. Let's hope they agree!