Forum Discussion
Question Measure - Count SUM and Exclude from other status (Total Count)
Hi Team,
Have just started my PowerBi Journey, and have a Q.
I have 2 Columns. KeyValue and Status
KeyValue are Unique IDs
Status = Pending; Exception; Completed (see table below for details)
Question?
How can I calculate System Exception SUM(total) Keyvalues (execlude if they already in COMPLETE status)
| KeyValue | Status |
| ID- 111 | System Exception |
| ID- 111 | Business Exception |
| ID- 111 - Review | Completed |
| ID- 222 - Review | Completed |
| ID- 333 | Completed |
| ID-444 | System Exception |
| ID-555 | System Exception |
| ID-666 | Business Exception |
| Results (Pie Chart) | |
| Completed (with Review in KeyValue) = | 2 |
| System Exception = | 2 |
| Business Exception = | 1 |
| Total KeyValue = | 6 |
5 Replies
Create 2 different measures. The first measure is your base measure which will do the sum/count etc. It might be something like:
Total Records = COUNTROWS('Your Table Name')(this will give you a record count, alternatively you could use DISTINCTCOUNT('Your Table Name'[KeyValue]) if you want uniques only
Next you'll write a measure that calls the base measure but excludes items you don't want:Count excluding completed = CALCULATE( [Total Records], 'Your Table Name'[Status] <> "Completed" )- Idrissshatila
Super User
Hello hsiddiq ,
it's true as Anonymous , but it could be written as one measure, no need for two measures
Count excluding completed = CALCULATE( countrows('YourTableName'), 'Your Table Name'[Status] <> "Completed" )Don't write it as one measure. If you do that you will get the wrong answers in certain aggregation situations.
hsiddiq Idrissshatila
