Forum Discussion

shayla's avatar
shayla
Frequent Visitor
2 years ago
Solved

Exclude certain rows in total with negative values

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: ...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    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] )
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
2 years ago

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] )