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
Solved! Go to Solution.
Assuming your table is called "Table":
Measure = CALCULATE(SUM('Table'[QTY]);'Table'[Status]<>"C";'Table'[Status]<>"D")
CALCULATE() allows multiple filters
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 @v-shex-msft. Thankyou very much for the reply.
Something like this should work:
Sum = CALCULATE(SUM(Qty);FILTER(Status=C))
Thanks for the reply @Douwe but I don't think that will filter out the D's as well.
Extend it like this: Sum = CALCULATE(SUM(Qty);FILTER(Status<>"C" || Status<>"D"))
Assuming your table is called "Table":
Measure = CALCULATE(SUM('Table'[QTY]);'Table'[Status]<>"C";'Table'[Status]<>"D")
CALCULATE() allows multiple filters
Check out new user group experience and if you are a leader please create your group
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.
User | Count |
---|---|
431 | |
179 | |
103 | |
88 | |
60 |
User | Count |
---|---|
442 | |
194 | |
140 | |
109 | |
86 |