Exclude certain rows in total with negative values

[shayla]

Hi there!

I am trying to exclude certain rows in total with negative values. but it seems that the total is still including the negative values.

Could anyone help?

Calculation as below:

Order not Sales MTD = CALCULATE(if([MTD Order]-[MTD Sales]>0,[MTD Order]-[MTD Sales],BLANK()),Pillar[BU2]<>"Ortho")

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but please try something like below whether it suits your requirement.

Order not Sales MTD =
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE ( fact_table, business_unit[Business Unit], product[Product Cat] ),
        "@metrics",
            CALCULATE (
                IF ( [MTD Order] - [MTD Sales] > 0, [MTD Order] - [MTD Sales], BLANK () ),
                Pillar[BU2] <> "Ortho"
            )
    )
RETURN
    SUMX ( _t, [@metrics] )