Simple Sum Filter

[Spencer]

Hi

If I have a dataset like the following:

Status         Qty

P                   10

P                   20

M                  50

M                  30

C                   25

C                   20

D                   15

What would be the formula for a measure that calculates the total Qty excluding those with the status of C or D.

I'd like to be able to apply the same sort of concept but with many various status' and only have a few be excluded. 

Thanks

[v-shex-msft]

Hi Spencer,

SanderBeukers’s reply seems well, you could also use below measures:

Total Qty without CD = SUMX(FILTER(ALL(Test),AND([Status]<>"C",[Status]<>"D")),[Qty])

Sum Except CD = CALCULATE(SUM([Qty]),FILTER(ALL(Test),AND([Status]<>"C",[Status]<>"D")) )

Result: (It have added two columns to display the measures)

 

Notice: my test table is ‘test’, you can modify it to your table name.

Regards,

Xiaoxin Sheng

[Spencer]

I might try that method in the future @v-shex-msft. Thankyou very much for the reply.

[Douwe]

Something like this should work:

Sum = CALCULATE(SUM(Qty);FILTER(Status=C))

[Spencer]

Thanks for the reply @Douwe but I don't think that will filter out the D's as well.

[Douwe]

Extend it like this: Sum = CALCULATE(SUM(Qty);FILTER(Status<>"C" || Status<>"D"))

[SanderBeukers · Accepted solution]

Assuming your table is called "Table":

Measure = CALCULATE(SUM('Table'[QTY]);'Table'[Status]<>"C";'Table'[Status]<>"D")

CALCULATE() allows multiple filters