Weighted Average

[Charles]

Hi,

I need to understand how to implement a custom aggregate function which performs a weighted average. Or at the very least, a sum product. Ideally the user should be able to select both the item and then the item to weight it by.

Is this even possible? If yes, can you please provide an example of how it can be achieved.

I notice that the the official marketing spiel at WPF Pivot Grid mentions weighted averages within the Calculations section. However, it's not obvious to me how this can be done.

Thanks in advance,

Charles

[Kalin]

Hi Charles,

By using the Index calculation you should achieve the desired. You can apply it from the UI by clicking on the selected aggregate value in the PivotFieldList and selecting the Index item (check the screenshot). You can achieve the same from XAML by using the following snippet:

<pivot:LocalDataSourceProvider.AggregateDescriptions>
    <pivot:PropertyAggregateDescription PropertyName="Net" AggregateFunction="Sum">
        <pivot:PropertyAggregateDescription.TotalFormat>
            <pivot:Index/>
        </pivot:PropertyAggregateDescription.TotalFormat>
    </pivot:PropertyAggregateDescription>
</pivot:LocalDataSourceProvider.AggregateDescriptions>

For more details please check the following article from our online help documentation (look for the Index calculation):
http://docs.telerik.com/devtools/wpf/controls/radpivotgrid/features/localdatasourceprovider/calculations.html#calculations

Hope this helps.

Regards,
Kalin
Telerik

[Charles]

Hi Kalin,

 Thanks for your response. However, this is not really what I am looking for. I need to perform a weighted average in terms of the aggregation itself. Not as a post-step calculation. I also need to select the item by which to weigh it.

 As an example, this could be achieved in SQL like so:

select category, sum(item * weightItem) / sum(weightItem) as weightedAverage
from tableName
group by category

In C# it could be implemented as an extension method. E.g.:

public static double WeightedAverage<T>(this IEnumerable<T> source, Func<T, double> itemSelector, Func<T, double> weightSelector)
{
    var result = source.Aggregate(Tuple.Create(0.0, 0.0), (a, t) => Tuple.Create(a.Item1 + (itemSelector(t) * weightSelector(t)), a.Item2 + weightSelector(t)));

    return result.Item1 / result.Item2;
}

Is there any way to somehow plug in this custom aggregation? 

Also worth pointing out, I'm using a QueryableDataProvider even though the data I'm working against is an IEnumerable (I'm simply performing an AsQueryable). The LocalDataSourceProvider did not perform very well when I first tried it (it loads everything into memory).

Thanks,

Charles

 

[Kalin]

Hi Charles,

Can you please check this article and let us know if this is what you need to achieve (I'm also attaching a sample excel pivot demonstrating the solution)? Please note that the PivotGrid is designed to display only grouped, aggregated and calculated values of the underlying data - not the data itself. If you need to display the underlying data as it is and just calculate the weighted average that won't be possible.

However if you can share some sample data and the expected result (a screenshot from example) we will be glad to further investigate the scenario and think of solution. Also if you can show what you need to achieve using the attached excel file, would be really helpful.

I'm looking forward to hearing from you.

Regards,
Kalin
Telerik

[Charles]

Hi Kalin,

Thanks for your help and the example Excel spreadsheet.

I tried approaching the problem from a different angle and came up with a reasonable solution. It involved creating a calculated field in my datasource up front (gradeItem*weightItem) then applying a custom calculated field after the fact. In this particular case I created a class named WAvgCaclulatedField which inherits from CalculatedField. Within its CalculateValue method I'm able to obtain the aggregate value of both the initial calculated field and the weight. I then simply divide the first value by the weight and I have my weighted average.

The only caveat is that the weighted average calculated field requires that both the item and the weight "be on screen" as fields. I.e. I can't hide them and only show the weighted average.

But I'm happy with this result.

Regards,

Charles

[Kalin]

Hello Charles,

I'm glad you have managed to achieve the desired. However if are willing to share a sample project demonstrating the solution, we will be glad to check it and see if there is anything we can suggest in order to improve the implementation.

If you have any other questions or concerns, please let us know.

Regards,
Kalin
Telerik