Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |