Forum Discussion
Need Help: Aggregation based on Multiple Sources as a Percentage
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 |
6 Replies
Hello Anonymous ,
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" ) )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 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 )
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
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
