Calculation: margin percentage

[Marco]

Hello,

I have need to get a percentual margin using a calculation, but it results in 'NaN' values. How can I achieve this?

 <pivot:PropertyAggregateDescription CustomName="Net Price" PropertyName="NetPrice" StringFormat="#,0.00,"/>

 <pivot:PropertyAggregateDescription CustomName="Net Margin" PropertyName="NetMargin" StringFormat="#,0.00," />
 <pivot:PropertyAggregateDescription CustomName="%" PropertyName="NetMargin" StringFormat="p">
      <pivot:PropertyAggregateDescription.TotalFormat>
           <pivot:PercentDifferenceFrom Axis="Columns" GroupName="Net Price" />
      </pivot:PropertyAggregateDescription.TotalFormat>
  </pivot:PropertyAggregateDescription>

The expected result should be NetMargin / NetPrice

[Rosen Vladimirov]

Hello Marco,

Do you have a column called "Net Price"? The essential part is in GroupName definition - as described in our online help:
"GroupName - this is the exact name of the group based on which the calculation will be applied. Note that this is a particular header in the rows or columns. For the above example we have defined "Name" as PropertyGroupDescription in RowGroupDescriptions collection. But the GroupName that has to be set when the Axis is Rows is one of the RowHeaders: "Pen", "Pencil" or "Notebook"."

So if you don't have a column with header "Net Price" it is expected to see NaN. Also you can use Axis and Level values to define the exact position of the GroupName. More information how to do it is available in our online help.

To illustrate all of the above I have implemented a simple project for you - please find it attached and inform me if you still have any problems or concerns.

Regards,
Rosen Vladimirov
the Telerik team

[Marco]

Hello Rosen Vladimirov,

Thanks for the quick reply.
I do not want a difference from something in another group. I want to calculate the margin percentage and accumulate it:

Group A Group B
      Price      Margin             %         Price      Margin              %
Row A 21 8 38% 20 6 30%
Row B 24 7 29% 21 9 43%
Row C 25 9 36% 24 5 21%

[Rosen Vladimirov]

Hello Marco,

Currently we don't have easy way to set such CalculationField. This feature is in our to-do list, but I cannot give you exact timeframe for it.
But there is a way to achieve this functionality and we have implemented it in the attached project. First you have to create CustomAggregateDescription and override some if its methods. The most important part is overriding of GetValueForItem method and setting the AggregateFunction of the object. This aggregate function has to be custom as well - PercentCalculationAggregate. The methods that have to be overriden are CreateAggregate (it is used to create an object which will hold and calculate the value that we'll put in the cell), GetStringFormat (we have set it to default value - percent), ToString (to have a custom string in RadPivotGrid and RadPivotFieldList instead of the type). CreateAggregate method is of type AggregateValue. We have to create a new custom AggregateValue and this is where we will implement the logic of the calculation. That's why in this class we have created two private properties - margin and value - they will be accumulated (in AccumulateOverride method) to summarize all values that we'll calculate. The value that we'll show in the cell is taken from the method GetValueOverride (in our case this will be margin / value). The last method that we've overriden is MergeOverride - it is used to accumulate all values for an item which has subitems.


We fully understand that this is not an easy solution, but you can check the result of it in the attached application. Do not hesitate to contact us if you have any problems or concerns.

Regards,
Rosen Vladimirov
the Telerik team

[Marco]

Thank you! Works perfectly! :)

[Greg]

This example does NOT work; no custom column is added to the PivotGrid showing the calculated margin %. Please compile and run the example and explain how this is supposed to work.

[Rosen Vladimirov]

Hi Greg,

The example you are referring is using older version of our dlls. With the official release we have applied some changes, so now when a Group Description doesn't have a PropertyName, it is automatically removed. In the project I've sent in my last mail, the Calculated field didn't have PropertyName, so you just have to add a valid value for this property:

<local:MarginAggregate PropertyName="Quantity" CustomName="% Margin"/>

This way it will work fine. Please try it and inform us if you still have any problems or concerns.

Greetings,
Rosen Vladimirov
the Telerik team