Sum IF < than x then sum

Question

I am trying to create a new measure within Power BI and although the syntax I have is correct in the example below, the value is way off what it should be which suggests the calculation is incorrect. I'm not too sure how to amend this to show the correct logic.

This is what I currently have. Any help is would be appreciated, please.

What I am trying to say is, is the amended_amount is less than or equal to 100,000, then 100,000 minus sum(app_value - comp_value)

cost = SUMX ( fact_cost, SWITCH ( TRUE (), CALCULATE(fact_cost[amended_value]) <=100000, calculate(100000 - SUM(fact_cost[app_value]) - sum(fact_cost[comp_value]) )))

@AJ_25 It seems that you are testing only one condition, and you don't need to use switch function. A normal if function would be more than enough. — Ozan Sen
This expression goes through each row in the table fact_cost and every row that has fact_cost[amended_value]) <=100000 fulfills calculation - 100000 -fact_cost[app_value]- fact_cost[comp_value] (all values comes from a current row not from a table) all other are zero value. Then the result of the calculation is added to a total. It's like SUMX=(row1_result+row2_result + ....). If it is not what you want then give a formula or a sample result — Mik

Ozan Sen Ozan Sen · Accepted Answer · 2022-07-26T12:30:06

I think This is what you want. Please test it.

cost =
SUMX (
    fact_cost,
    IF (
        fact_cost[amended_value] <= 100000,
        CALCULATE (
            100000 - SUM ( fact_cost[app_value] )
                - SUM ( fact_cost[comp_value] )
        )
    )
)

Sum IF < than x then sum

1 Answers