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:

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

 

 

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

1 Reply

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