Help: Calculate a Percentage Conditional

[Anonymous]

Looking for some guidance related to another post.

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..help?

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

[v-alq-msft]

Hi, @Anonymous 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[v-alq-msft]

Hi, @Anonymous 

Based on your description, you may two calculated columns as follows.

Normal process = 
MAXX(
     FILTER(
         ALLSELECTED('Table'),
         'Table'[Unique Record] = EARLIER('Table'[Unique Record])&&
         EARLIER('Table'[Activity Type]) = "Waiting Room"&&
         'Table'[Activity Type] = "Seen by Doctor"
     ),
     'Table'[Unique Record]
)

Abnormal process = 

var _currentrecord = CALCULATE(MAX('Table'[Unique Record]))
Return
MAXX(
    FILTER(
        ALLSELECTED('Table'),
        EARLIER('Table'[Activity Type]) = "Seen by Doctor"&&
        CALCULATE(
        COUNTROWS(
                    FILTER(
                           ALLSELECTED('Table'),
                           'Table'[Activity Type] = "Waiting Room"&&'Table'[Unique Record]=_currentrecord
                           )
        )) = 0
        
    ),
    
    IF(
        'Table'[Unique Record] = _currentrecord,
        'Table'[Unique Record]
    
)
)

Then you can create a measure as below.

Percentage = 
DIVIDE(
     COUNT(
         'Table'[Normal process]
     ),
     DISTINCTCOUNT('Table'[Unique Record])-COUNT('Table'[Abnormal process])
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

When I try to implement this I get a error as it creates a circular dependency with another column I created to use to filter. Any work arounds?

[v-alq-msft]

Hi, @Anonymous 

Most of time, circilar dependencies occur when you use calculated tables. You may avoid them by paying attention to your choice of function. I wonder tables, their relationships in the data models and the error message. Do mask sensitive data before uploading.

For further information, you may refer to the following link. Thanks.

https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/

Best Regards

Allan

[amitchandak]

First Create a column

Seen After Waiting = MINX(filter(Sheet1,Sheet1[Unique Record]=EARLIER(Sheet1[Unique Record]) && EARLIER(Sheet1[Activity Type])="Waiting Room" && Sheet1[Activity Type] ="Seen by Doctor"),Sheet1[Unique Record])

Then a measure

Ratio = DIVIDE(COUNT(Sheet1[Seen After Waiting]),COUNTX(filter(Sheet1,Sheet1[Activity Type]="Seen by Doctor"),Sheet1[Unique Record]) )

Please find solution at

https://www.dropbox.com/s/dgbvvdl9wwivdac/waiting.pbix?dl=0