How to cap total sum of column

[Bruno_Soares_25]

Hello,

Basically i've got two formulas

(Calculated Column) AUX_Cost_Deal = IF(Metrics[DB_Channel] = "Deal" , Metrics[CM360 Filtered Clicks]*0.35)
(Calculated Measure) DB_Total Cost = SUM(Metrics[Cost]) + SUM(Metrics[AUX_Cost_NL])+ SUM(Metrics[AUX_Cost_Deal])

Problem is that I need to cap the sum of AUX_Cost_Deal never to be above 1500 but i've been struggling with it. 

In DAX Studio this 

EVALUATE
ROW (
    "Total Deal Cost",
    MIN (
        2000,
        CALCULATE (
            SUM ( Metrics[AUX_Cost_Deal] ),
            FILTER (
                ALL ( Metrics ),
                SEARCH ( "Deal", Metrics[AUX_DIM_dbChannel], 1, 0 ) >= 1
            )))))

Returns 2000 but cannot replicate this in Power BI

[DataNinja777]

Hi @Bruno_Soares_25 ,

You can solve your problem by writing a measure like below:

DB_Total Cost = 
VAR vBaseCost = SUM(Metrics[Cost])
VAR vAuxNLCost = SUM(Metrics[AUX_Cost_NL])

// Calculate the uncapped deal cost.
// The data for the 3 'Deal' rows sums to: 350 + 700 + 1050 = 2100
VAR vAuxDealCost_Uncapped = SUM(Metrics[AUX_Cost_Deal])

// Apply the 1500 cap
VAR vAuxDealCost_Capped = MIN(1500, vAuxDealCost_Uncapped)

RETURN
    vBaseCost + vAuxNLCost + vAuxDealCost_Capped

The resultant output shows that it costs exceeding 1,500 were capped.  

I have attached an example pbix file for your reference.

Best regards,

[Praful_Potphode · Accepted solution]

HI @Bruno_Soares_25 

convert your AUX_COst_Deal to a measure like below:

AUX_Cost_Deal_Measure =
SUMX(
    FILTER(Metrics, Metrics[DB_Channel] = "Deal"),
    Metrics[CM360 Filtered Clicks] * 0.35
)

Then calculate DB_Total Cost like below:

DB_Total Cost =
VAR BaseCost = SUM(Metrics[Cost])
VAR AUX_Cost_NL_Total = SUM(Metrics[AUX_Cost_NL])
VAR CalculatedDealCost = [AUX_Cost_Deal_Measure]
VAR CappedDealCost = MIN(CalculatedDealCost, 1500) // The key capping logic

RETURN
    BaseCost + AUX_Cost_NL_Total + CappedDealCost

As you can see in the logic we take min of the two which should cap the value for you.

Please Give Kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[AnalyticPulse]

can you give more context to this, sample data or something to understand better. @Bruno_Soares_25