Calculation: margin percentage

7 posts, 1 answers
  1. Marco
    Marco avatar
    3 posts
    Member since:
    Jul 2012

    Posted 11 Jan 2013 Link to this post

    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
  2. Rosen Vladimirov
    Admin
    Rosen Vladimirov avatar
    640 posts

    Posted 11 Jan 2013 Link to this post

    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

    Explore the entire Telerik portfolio by downloading Telerik DevCraft Ultimate.

  3. UI for WPF is Visual Studio 2017 Ready
  4. Marco
    Marco avatar
    3 posts
    Member since:
    Jul 2012

    Posted 11 Jan 2013 Link to this post

    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%

  5. Answer
    Rosen Vladimirov
    Admin
    Rosen Vladimirov avatar
    640 posts

    Posted 11 Jan 2013 Link to this post

    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

    Explore the entire Telerik portfolio by downloading Telerik DevCraft Ultimate.

  6. Marco
    Marco avatar
    3 posts
    Member since:
    Jul 2012

    Posted 11 Jan 2013 Link to this post

    Thank you! Works perfectly! :)
  7. Greg
    Greg avatar
    29 posts
    Member since:
    Oct 2007

    Posted 06 Mar 2013 Link to this post

    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.
  8. Rosen Vladimirov
    Admin
    Rosen Vladimirov avatar
    640 posts

    Posted 07 Mar 2013 Link to this post

    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

    Explore the entire Telerik portfolio by downloading Telerik DevCraft Ultimate.

Back to Top
UI for WPF is Visual Studio 2017 Ready