topic Exclude certain rows in total with negative values in DAX Commands and Tips

Exclude certain rows in total with negative values

shayla — Wed, 20 Mar 2024 03:26:42 GMT

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

Re: Exclude certain rows in total with negative values

Jihwan_Kim — Wed, 20 Mar 2024 03:53:16 GMT

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