如何在Power Pivot（DAX）中实现堆叠式减除累加层级的计算？

Got it, let's fix this tiered pricing calculation for you. Your current formula picks a single price tier based on the minimum matching threshold, but stacked subtractive accumulation (think "volume tier pricing" where each tier's quantity is counted separately and summed) requires us to calculate each applicable tier's contribution and add them all up.

How Stacked Tiered Pricing Works (Quick Recap) #

For example, if your price tiers are:

• 0-100 units: $5 each
• 101-300 units: $4 each
• 301+ units: $3 each

A quantity of 500 would calculate as:
(100 * $5) + (200 * $4) + (200 * $3) = $500 + $800 + $600 = $1900

The DAX Formula You Need #

Stacked Tier Total Price = 
VAR _currentQuantity = [Quantity]
VAR _currentYearProduct = Quantity2022[YearProduct]
// Step 1: Get all relevant price tiers for the current product/year that apply to our quantity
VAR _applicableTiers = 
    FILTER(
        PriceList,
        PriceList[YearProduct] = _currentYearProduct && PriceList[QFrom] <= _currentQuantity
    )
// Step 2: Add upper bounds for each tier (calculate where this tier ends)
VAR _tiersWithUpperLimits = 
    ADDCOLUMNS(
        _applicableTiers,
        "@QTo", 
        // Find the next tier's starting quantity, subtract 1 for this tier's upper limit
        VAR _nextTierStart = MINX(FILTER(_applicableTiers, PriceList[QFrom] > EARLIER(PriceList[QFrom])), PriceList[QFrom])
        // If it's the last tier, use our total quantity as the upper limit
        RETURN IF(ISBLANK(_nextTierStart), _currentQuantity, _nextTierStart - 1)
    )
// Step 3: Calculate quantity and total for each tier
VAR _tierCalculations = 
    ADDCOLUMNS(
        _tiersWithUpperLimits,
        "@TierQuantity", MAX(0, @QTo - PriceList[QFrom] + 1), // Ensure we don't get negative values
        "@TierTotal", @TierQuantity * PriceList[Price]
    )
// Step 4: Sum all tier totals for the final price
RETURN SUMX(_tierCalculations, [@TierTotal])

Key Breakdown of the Formula #

• _applicableTiers: Filters down to only the price tiers that apply to our product/year and are within our quantity range (no need to consider tiers that start above our total quantity).
• _tiersWithUpperLimits: Most price lists only define the start of a tier (QFrom), so we calculate the end of each tier by looking at the next tier's start value. For the highest tier, we use our total quantity as the upper bound.
• _tierCalculations: Computes how many units fall into each tier (upper limit minus lower limit plus 1 to include both endpoints) and multiplies by the tier's price to get the tier's total cost. The MAX(0,...) handles edge cases where quantity exactly matches a tier's start.
• SUMX: Aggregates all the tier totals to get the final stacked price.

Simplification If You Have a QTo Column #

If your PriceList table already includes a QTo column (defining the upper limit of each tier), you can skip calculating the upper limit manually. Just replace the @QTo line with:

"@QTo", IF(ISBLANK(PriceList[QTo]), _currentQuantity, PriceList[QTo])

内容的提问来源于stack exchange，提问作者CJW1960