Distribute a Target by Product Group with DAX in Excel Power Pivot
Question details
Create a DAX formula that distributes an increased target across customers and product groups in proportion to their base values.

- 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.
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%.
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.
In Excel, select the Power Pivot tab, then choose Measures > New Measure.
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.
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.
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.

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.

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.




