cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gwtham90
Helper I
Helper I

Calculate percentage from different columns in the same table

Hi,

I have the below data in a table where I'm trying to find the percentage of "Patients Who agreed for recommendations" and the count of patients who agreed for recommendations.

Patient  Recommendations  AgreedForTherecommendation
1                A                                 B
1                B                                 B
2                A                                A
2                A                                C
2                C                                A
3                D                                C
4                A                                A

 

Finding the count value was simple using DAX fomula -Measurecount  =  CALCULATE(DISTINCTCOUNT(Table1[Patient])). I was trying different DAX formula to get the percentage but the results are not matching the below output.

 

For calculating percentage I was trying the below DAX

Measure Percentage = 
VAR allcid= CALCULATE(DISTINCTCOUNT (Table1[Recommendations]))

VAR
YesCID = CALCULATE(DISTINCTCOUNT(Table1[Patient]))

VAR RESULT = DIVIDE(YesCID,allcid, 0)
RETURN
IF(ISBLANK(RESULT),0,RESULT)
 
I'm not sure how to display the below result using the one of the column from the same table.
 

In simple I need the get the below output.

AgreedForTherecommendation    MeasureCount       MeasurePercentage
A                                                       2                           66.6%
B                                                       1                           100%
C                                                       2                           200%

Any help is much appretiated. Thanks

1 ACCEPTED SOLUTION
parry2k
Super User III
Super User III

@gwtham90 here are your measures:

 

Count = DISTINCTCOUNT ( 'Table'[Patient] )

% = 
DIVIDE ( 
    [Count], 
    CALCULATE ( 
        DISTINCTCOUNT ( 'Table'[Patient] ), 
        ALL ( 'Table' ), 
        TREATAS ( VALUES ( 'Table'[AgreedForTherecommendation] ), 'Table'[Recommendations] ) 
    )
)

 

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

1 REPLY 1
parry2k
Super User III
Super User III

@gwtham90 here are your measures:

 

Count = DISTINCTCOUNT ( 'Table'[Patient] )

% = 
DIVIDE ( 
    [Count], 
    CALCULATE ( 
        DISTINCTCOUNT ( 'Table'[Patient] ), 
        ALL ( 'Table' ), 
        TREATAS ( VALUES ( 'Table'[AgreedForTherecommendation] ), 'Table'[Recommendations] ) 
    )
)

 

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors