Need Help: Aggregation based on Multiple Sources as a Percentage

[Ops_Data123]

I will start with what I am trying to accomplish: 

Based on the type of activity I want to track how many progress to the next stage. I.e. 40% of patients who appear in waiting room move to ER, 98% of patients who enter waiting room are seen by a physician. 

The way the data is structured is formatted below. Esentially, we can have multiple lines for the same patient ID indicating that they have been in the ER 2-3 times, when they really are only in the waiting room and ER 1 time. I esentially want the data to say if they hit ER 1 otherwise 0 and same for each category. 

I have tried to create a concatenated column and do a distinct count by that, which works but it does not help me with percentages and I cannot find anything posted. I want to maintain all of my relationships that exist in the table this data is stored in, so I am trying to avoid creating a unique table if possible... 

In the below example I would expect the following:

Waiting Room to Seen by DR: 100% (2/2)=1

Waiting Room to ER: 50% (1/2)=.5

Unique Record	Activity Type	Concat	Patient
123	ER	123-ER	John Doe
123	Waiting Room	123-Waiting Room	John Doe
123	Waiting Room	123-Waiting Room	John Doe
123	Seen by Doctor	123-Seen by Doctor	John Doe
123	Seen by Doctor	123-Seen by Doctor	John Doe
123	Seen by Doctor	123-Seen by Doctor	John Doe
234	Seen by Doctor	234-Seen by Doctor	Chrissy Lee
234	Waiting Room	234-Waiting Room	Chrissy Lee

[v-gizhi-msft]

Hi,

According to your description, please try these two measures:

Waiting Room to ER = 
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Unique Record] ),
        FILTER ( 'Table', 'Table'[Activity Type] = "ER" )
    ),
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Unique Record] ),
        FILTER ( 'Table', 'Table'[Activity Type] = "Waiting Room" )
    )
)

Waiting Room to Seen by Doctor = 
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Unique Record] ),
        FILTER ( 'Table', 'Table'[Activity Type] = "Seen by Doctor" )
    ),
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Unique Record] ),
        FILTER ( 'Table', 'Table'[Activity Type] = "Waiting Room" )
    )
)

 Choose card visuals and the result shows:

Best Regards,

Giotto Zhi

[Ops_Data123]

So I realize that I left one piece out. I do not want to count items that only reside in waiting room. Example, this works because they both progressed somewhere in the case that they do not move from waiting room how can I exclude that from the denominator? For example:

What I am showing now: Waiting Room to Seen by Doctor= 2/2 = 100% 

What I want to see: Waiting Room to Seen by Doctor= 1/2=50% 

In this example, 456 went straight to doctor and did not actually progress from waiting room therefore I do not want that counted in my metric

Unique Record	Activity Type	Concat	Patient
456	Seen by Doctor	456-Seen by Doctor	John Doe
123	Waiting Room	123-Waiting Room	John Doe
234	Waiting Room	234-Waiting Room	John Doe
123	Seen by Doctor	123-Seen by Doctor	John Doe

[danextian]

Hello @Ops_Data123 ,

You can do something like this:

Waiting Room to Seen by Doctor = 
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Unique Record] ),
        'Table'[Activity Type] = "Waiting Room"
    ),
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Unique Record] ),
        'Table'[Activity Type] = "Seen by Doctor"
    )
)

Or use a separate measure for the count of unique records and then reference that in another measure.

Count of Unique Records =
DISTINCTCOUNT ( 'Table'[Unique Record] )

Waiting Room to Seen by Doctor = 
DIVIDE (
    CALCULATE (
        [Count of Unique Records],
        'Table'[Activity Type] = "Waiting Room"
    ),
    CALCULATE (
        [Count of Unique Records],
        'Table'[Activity Type] = "Seen by Doctor"
    )
)

[Ops_Data123]

Similar to below,

So I realize that I left one piece out. I do not want to count items that only reside in waiting room. Example, this works because they both progressed somewhere in the case that they do not move from waiting room how can I exclude that from the denominator? For example:

What I am showing now: Waiting Room to Seen by Doctor= 2/2 = 100% 

What I want to see: Waiting Room to Seen by Doctor= 1/2=50% 

In this example, 456 went straight to doctor and did not actually progress from waiting room therefore I do not want that counted in my metric

Unique Record	Activity Type	Concat	Patient
456	Seen by Doctor	456-Seen by Doctor	John Doe
123	Waiting Room	123-Waiting Room	John Doe
234	Waiting Room	234-Waiting Room	John Doe
123	Seen by Doctor	123-Seen by Doctor	John Doe

[danextian]

You can create a calulated column to indicate whether a record has gone into "Waiting Room"

Waited? =
IF (
    NOT (
        ISBLANK (
            // if waiting room count is blank, the record has skipped this activity
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Unique Record] ),
                ALLEXCEPT ( 'Table', 'Table'[Unique Record] ),
                'Table'[Activity Type] = "Waiting Room"
            )
        )
    ),
    "Yes",
    "No"
)

And then this measure to compute for the percentage:

Waited and Seen =
VAR __waited_and_seen =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Unique Record] ),
        'Table'[Waited?] = "Yes",
        'Table'[Activity Type] = "Seen by Doctor"
    )
VAR __seen =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Unique Record] ),
        'Table'[Activity Type] = "Seen by Doctor"
    )
RETURN
    DIVIDE ( __waited_and_seen, __seen )

[Ops_Data123]

This is addressing where they have went to the waiting room but does not address if they were in waiting room skipped seeing doctor and went to ER.

How to I incorporate if for the same unique ID they hit both Waiting Room AND ER or Waiting AND Doctor?

In this case= Waiting to Doctor = 0 

Waiting to ER= 100%