Views:

## Scenario Overview

The following is a discount pricing example for a Configure Price Quote (CPQ) Scenario.

A different discount applies for each band:

 Band 1 - Purchase 0-10 items 0% Band 2 - Purchase 11-50 items 22.5% Band 3 - Purchase 51-100 items 40.5% Band 4 - Purchase 101-200 items 58.5% Band 5 - Purchase 201- items 67.5%
• Every product has a different set of discounts and potentially different bands.
• To complicate it a bit more each product has a margin field (%) and the discounts are only applied to the margin on that product
• Local products will be 100% margin but the 3rd party products might have a margin of 70% for example
• So if the product sells at \$100 and my margin is 70% then discounts only apply to \$70 of the product

For this article it is assumed that you have at least basic familiarity with Decision Tables and/or have read the following articles:

Note: We will not detail step-by-step instructions here on how to set up ConditionsActions or Multi-Sheet Decision Tables, please read the above articles if you need detailed configuration steps.

The Multi-Sheet Decision Table will have to evaluate the band depending on the Quantity

To do this we will create 3 Decision Tables Sheets

• Band Discounts
• Determine Bands
• Calculate Discount

## North52 Decision Suite Solution

The North52 Decision Suite solution works like this:

• A formula of type Save - Perform Action is created on the Opportunity Product entity
• When either the Price Per Unit or Quantity fields change:
• The first Decision Table sheet calculates the band discounts
• The second Decision Table sheet determines which band should be used
• The third Decision Table sheet calculates the discount using the bands and band discounts and then updates the Manual Discount Amount field

### Create the Formula

• Create a new formula, setting the following values in the Formula Guide
• Source Entity set to Opportunity Product
• Set Formula Type to Save - Perform Action
• Select the Decision Table editor
• Click create
• Change the Name of the formula to CPQ - Custom Pricing - Training Courses
• Set the Event to Update
• Expand the Source & Target section
• Control-click to multi-select the Price Per Unit and Quantity fields
• Click Save

### Global Calculations Sheet

The Calculations Sheet has a single calculation: Product Margin - used to calculate product margin by subtracting the standard cost from the price per unit.

• Right-click and select Operations > Toggle Advanced mode to show the Global Calculations sheet
• Select the Global Calculations sheet
• Select cell A2 and type Product Margin
• Select cell B2, go to the Source tab and expand the Source node
• Click on Price Per Unit, then type a -
• Close the Source node and expand the Related (N:1) node
• Expand product (productid)
• Expand (Attributes)
• Click on Standard Cost and replace the ? with 0 to set 0 as the default value if the Standard Cost field is null
• Cell B2 should now look like:
`[opportunityproduct.priceperunit]-[opportunityproduct.productid.standardcost.0]`
• Click Save
• Right-click and select Operations > Toggle Advanced mode to hide the Global Calculations sheet

### Sheet 1: Band Discounts

• Rename the existing Decision Table sheet to Band Discounts
• Select columns A and B, then right-click and select Delete > Delete Column as we do not need Conditions or Actions for this sheet
• Select all columns A to E, then right-click and select Insert > Insert Inline Calculation (==)
• Note: if you using a North52 BPA build less than 562 you will need insert the Inline Calculations one by one
• Rename row 2 for the columns:
• A: Band 1 Discount
• B: Band 2 Discount
• C: Band 3 Discount
• D: Band 4 Discount
• E: Band 5 Discount
• Set the discount levels in row 4
• Band 1: set to 0
• Band 2: type 0.225* then select Product Margin from the Global Calculations node in the Source tab
• Band 3: type 0.405* then select Product Margin again
• Band 4: type 0.585* then select Product Margin again
• Band 5: type 0.675* then select Product Margin again

### Sheet 2: Determine Band

• Right-click and select Insert > Insert Decision Table
• Select the new Decision Table sheet and rename to Determine Band
• Select column B, then right-click and select Delete > Delete Column as we do not need Actions for this sheet
• Select all columns to G, then right-click and select Insert > Insert Inline Calculation (==)
• Note: if you using a North52 BPA build less than 562 you will need insert the Inline Calculations one by one
• Rename row 2 for the columns:
• B: Band
• C: Band 1 Qty
• D: Band 2 Qty
• E: Band 3 Qty
• F: Band 4 Qty
• G: Band 5 Qty
• Select cell A2, go to the Source tab and search for quantity
• Expand the Source node and click on Quantity
• Select cell A4 and type ((0,10))
• Select cell B4 and type 1
• Select cell C4 and click on Quantity from the Source tab
• Complete the rest of the table as outlined in the screen capture below

### Sheet 3: Calculate Discount

Note: Please click on the refresh icon in the Source tab to update all the Decision Table Calculations

• Right-click and select Insert > Insert Decision Table
• Select the new Decision Table sheet and rename to Calculate Discount
• Right-click and select Hit Policy > Exit this Decision Table on First Match to uncheck this option as we want to evaluate all rows in this sheet
• Select cell A2, go to the Source tab and expand Decision Table Calculations and expand the Determine Band node
• Click on Band
• Select cell A4 and type >=1
• Select cell A5 and type >=2
• Select cell A6 and type >=3
• Select cell A7 and type >=4
• Select cell A8 and type >=5
• Select column B, then right-click and select Insert > Insert Increment Calculation (+=)
• Replace Set Name with Cumulative Discount
• Select cell B4, go to the Source tab and expand Decision Table Calculations
• Expand the Determine Band node and c​​​​lick on Band 1 Qty, then type a (multiply), then click on Band 1 Discount in the Band Discounts node
• Cell B4 should look like: {Band 1 Qty}*{Band 1 Discount}
• Select cell B5 and click on Band 2 Qty, then type a (multiply), then click on Band 2 Discount
• ​​​​​​​Select cell B6 and click on Band 3 Qty, then type a (multiply), then click on Band 3 Discount
• ​​​​​​​Select cell B7 and click on Band 4 Qty, then type a (multiply), then click on Band 4 Discount
• ​​​​​​​Select cell B8 and click on Band 5 Qty, then type a (multiply), then click on Band 5 Discount
• Select cell C2, go to the Source tab and search for manual
• Expand the Source node and click Manual Discount Amount
• ​​​​​​​Select cell C9, clear the search on the Source tab and expand the Decision Table Calculations node
• Click on Cumulative Discount
• ​​​​​​​Click Save

This table uses an Increment Calculation (Calculate Discount Sheet, Column B) variable to keep a running sum of the discount based on the results of the individual Decision Table Sheets. You can think of this as a variable result which is then used to update the Manual Discount Amount field on the Opportunity Product entity.

## Testing

Create a new Opportunity record and Opportunity Products to see the various discount levels being applied.

# Did you know?

## North52 TestShield allows rigorous regression testing of your Dynamics 365 system in-sprint

As well as being scheduled, or triggered from build pipelines, TestShield automated tests can be triggered manually for a quick mid-sprint regression test. This allows for quick testing of alternative ways to implement a requirement and measure the impact on existing processes.

It's quick to clone an existing test and try variations without affecting the original test set up too.