Forum Discussion
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
Assuming your table is called "Table":
Measure = CALCULATE(SUM('Table'[QTY]);'Table'[Status]<>"C";'Table'[Status]<>"D")
CALCULATE() allows multiple filters
6 Replies
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
I might try that method in the future Anonymous. Thankyou very much for the reply.
Something like this should work:
Sum = CALCULATE(SUM(Qty);FILTER(Status=C))
Assuming your table is called "Table":
Measure = CALCULATE(SUM('Table'[QTY]);'Table'[Status]<>"C";'Table'[Status]<>"D")
CALCULATE() allows multiple filters
