Function Problems

Distribute a Target by Product Group with DAX in Excel Power Pivot

Maira MehtabMaira Mehtab Nov 20, 2026 869 views

Question details

Create a DAX formula that distributes an increased target across customers and product groups in proportion to their base values.

Distribute a Target by Product Group with DAX in Excel Power Pivot
Product
Microsoft Excel Power Pivot
Device & OS
not provided
Scenario
A Power Pivot model contains base values by customer and product group, and the user wants to apply a 15% increase proportionally.
Observed behavior
Base values of 50, 30, and 20 should become targets of 57.5, 34.5, and 23, respectively.
Before you start

Identify the table and numeric column containing the base values in your Power Pivot data model. Confirm that customer and product-group fields filter that table and that the intended increase is 15%.

Solution 1Recommended

Create a DAX measure for the increased target

Use a measure when the target should respond to customer and product-group filters in a PivotTable.

Because the target is a uniform 15% increase on the total base, multiplying each filtered base value by 1.15 produces the same result as allocating the increased total by each group's share.

1
Open the measure dialog

In Excel, select the Power Pivot tab, then choose Measures > New Measure.

2
Enter the formula

Select the relevant table, name the measure Target, and enter =SUM('Sales'[Base Value]) * 1.15 in the formula field. Replace Sales and Base Value with your actual table and column names, then click OK.

3
Add the measure to the PivotTable

In the PivotTable Fields pane, add customer and product-group fields to Rows and add Target to Values. The measure calculates the target for each displayed filter context.

4
Check the results

Compare the Values area with the base figures: 50, 30, and 20 should produce 57.5, 34.5, and 23, for a total target of 115.

Create a DAX measure for the increased target
Check model relationships: If every group shows the same target, check the relationships in Power Pivot > Manage > Diagram View so the customer and product-group fields filter the base-value table.
Free Microsoft Office alternative

Calculate proportional targets in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface for working with spreadsheet files. Open an Excel workbook and use a standard formula to calculate a 15% increase when you do not need the Power Pivot DAX model.

Calculate increased targets with a simple spreadsheet formula such as =A2*1.15.Open and edit Microsoft Excel XLSX files for a smoother transition.Use a familiar spreadsheet interface that is easy to learn.Work in a free, lightweight office suite.
microsoft office alternative - wps office

Frequently Asked Questions

Why does multiplying each base value by 1.15 allocate the target proportionally?

The increased total is 1.15 times the base total. Allocating it by each group's share of the base total simplifies to each group's base value multiplied by 1.15.

Where do I enter the DAX formula in Excel?

Select Power Pivot > Measures > New Measure, choose the relevant table, and enter the formula in the measure dialog. Then add the measure to the PivotTable Values area.

What if the increase is not 15%?

Change the multiplier in the measure. For example, use 1.10 for a 10% increase or 1.20 for a 20% increase.

Can I use this measure if I have a separate fixed target total?

Not without changing the formula. A separate fixed target must be allocated using each group's share of the relevant base total; the correct DAX depends on how that target and the desired filter context are stored in the model.