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

#### Check it Out!

Click here to read more about the March 2021 Updates!

#### 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.

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors