Question Measure - Count SUM and Exclude from other status (Total Count)

[hsiddiq]

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

[RossEdwards]

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]

Hello @hsiddiq ,

it's true as @RossEdwards , but it could be written as one measure, no need for two measures

Count excluding completed = CALCULATE(
    countrows('YourTableName'),
    'Your Table Name'[Status] <> "Completed"
)

[RossEdwards]

Don't write it as one measure.  If you do that you will get the wrong answers in certain aggregation situations.
@hsiddiq @Idrissshatila 

[hsiddiq]

Thanks Ross, Your method works fine, with one slight issue (which I think I didn't explain properly because the table was confusing). the Unique ID when completed has this added ID- 111 - Review Status= Completed. So what I need to do is Compare the ID with removing the "- Review" part if it has. (sometimes it will not have that) hope this makes sense, and apologies my last table made it a bit confusing.

[RossEdwards · Accepted solution]

I'd suggest making a new column that only includes the elements that have the uniqueness you want.  I'd suggest doing this in Power Query. Then just update the provided code to work how you need it.