cancel
Showing results for
Search instead for
Did you mean:  Helper II

## Simple Sum Filter

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

1 ACCEPTED SOLUTION  Advocate I

Assuming your table is called "Table":

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

CALCULATE() allows multiple filters

6 REPLIES 6  Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.  Helper II

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

Something like this should work:

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

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

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

Assuming your table is called "Table":

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

CALCULATE() allows multiple filters  ## Helpful resources

Announcements #### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group #### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings. Top Solution Authors
Top Kudoed Authors
Users online (9,530)