# Complex Measures/Aggregations in Pivotgrid

1. ##### Jens
12 posts
Member since:
Aug 2014

Posted 08 Oct 2014 Link to this post

Dear Telerik team,

is it possible to define more complex measures/aggregations than a simple sum for FLAT DATA?
... e.g. a calculating percentages, ratios, margins, etc. based on given datasource fields?

Simplified Example:

#these are the source data records:

Product        Category                      SalesChannel    Costs    Revenue
P1                 Mountain Bikes             Internet             2000    3000
P2                 Mountain Bikes             Internet             1000    2000
P3                 Mountain Bikes             Store                 3000    5000
P4                 Mountain Bikes             Store                 5000    6000
P5                 Accessoires                  Store                   100      150
P6                 Accessoires                  Internet                 80      100
P7                 Accessoires                  Store                     50        60
P8                 Accessoires                  Internet                 10        15

# We want to show the gross margin for each combination of category and sales channel with the formula:
gross margin = (revenue-costs)/revenue

# This should lead to the following pivotgrid matrix as result:
All Sales Channels
Internet         Store        All Sales Channels
All Categories             Mountain Bikes             21,7%        28,6%        26,2%
Accessoires                   40,0%        27,3%        31,3%
All Categories                39,6%        27,3%        31,1%

Thanks a lot for your suggestions
and best wishes from Germany
Jens
2. ##### Georgi Krustev
3747 posts

Posted 09 Oct 2014 Link to this post

Hello Jens,

Currently, the measure function provides information only for the current value and accumulator. Hence only sum accumulation (reduction to be more precise) can be done. We will improve this for the upcoming RTM release of Kendo UI.

As to the specific questions, could you please elaborate more, for instance, on the "Internet" measure calculation? Let's pretend that you have a dedicated column that holds the "gross margin" calculation. How do you expect to produce the 21,7% value for the Mountain Bikes if you are using the value from this additional column? In other words, you've got those items:

Product        Category                      SalesChannel    Costs    Revenue  GrossMargin
P1                 Mountain Bikes             Internet             2000    3000         0.3333
P2                 Mountain Bikes             Internet             1000    2000         0.5

How will the "21,7 %" value be calculated?

As a side note, in term of performance, it will be better to calculate the Gross Margin values on the server using an additional column as it is faster than doing the same operation on the client.

Thanks in advance for the clarification.

Best regards,
Georgi Krustev
Telerik

3. ##### Jens
12 posts
Member since:
Aug 2014

Posted 09 Oct 2014 in reply to Georgi Krustev Link to this post

Dear Georgi,
thanks for the quick reply. You are right there was a mistake in the grid I accidently mixed the labels of the lines. Here the correct one:

All Sales Channels
Internet         Store        All Sales Channels
All Categories             Accessoires                   21,7%        28,6%        26,2%
Mountain Bikes              40,0%        27,3%        31,3%
All Categories                39,6%        27,3%        31,1%

However for ratios/percentages/margins you cannot calculate the measures on source record level (and therefore also not on the server).
This would lead mathematically to wrong results in your aggregations later on.
=> So if you try to calculate a margin for each record of your data source and then aggregate via AVG then you will get a wrong value.

The right way is the following (based on your question how to calc the margin for mountain bikes sold via internet):

1. For each cluster/split get all the related records:
so get all records where Category=MountainBikes and Sales Channel = Internet

2. For those records calculate: Margin = ( SUM(Revenues) - SUM(Costs) ) / SUM(Revenues)
=> which would be: 40% = (5000 - 3000) / 5000

This is also the way how an OLAP server works, when you define an MDX formula like [margin]=([revenue]-[cost])/[revenue].
It has simple measures, with simple aggregations like SUM/AVG/MIN/etc.
And it has calculated measures built using certain simple measures.

Something like this must be simulated for flat data. And it must be applied each time you do a drag & drop within the pivotgrid (create new break downs / combinations).

Best wishes
Jens

4. ##### Georgi Krustev
3747 posts

Posted 13 Oct 2014 Link to this post

Hello Jens,

Thank you very much for the clarification, as it helped to clear the situation out. We will further investigate the current implementation and limitations and will provide a better way to accomplish more complex calculations.

Regards,
Georgi Krustev
Telerik

5. ##### Ants
3 posts
Member since:
Dec 2012

Posted 10 Nov 2014 Link to this post

Hello, is there any update on complex aggregations and calculated fields?

This pivotgrid is really nice visually and has some nice features but we don't have a remote data source, we have a fully client side js webapp.

These are very fundamental features I think. For even the simplest of data sets, we need to be able to at least create a derived/calculated field such as (SUM(A) / SUM(B)) and then also be able to create semi-complex aggregates such as averages.

Are there any workarounds for this? We will most likely have to use another product for now.

6. ##### Georgi Krustev
3747 posts

Posted 12 Nov 2014 Link to this post

Hello,

We are planing (and in fact we are working on it) to provide an additional dataitem and aggregate-context information to the aggregate function allowing to perform more complex calculations. This will be available either in the upcoming RTM release of Kendo UI PivotGrid or some of the next internal builds.

Currently, I cannot provide any feasible workaround to accomplish the desired functionality.

Best regards,
Georgi Krustev
Telerik