Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Help: Calculate a Percentage Conditional

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 ...
Need Help
v-alq-msft's avatar
v-alq-msft
Community Support
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      v-alq-msft
      Community Support
      6 years ago

      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