Calculate Percentage within selected hospitals

[f_r_1998]

Hi everyone, 

for a dashboard I need to calculate the percentage of hospitals that belong to a specific group of scores within all hospitals selected. There a basically three groups (ranges) of the hospital score: 0 - 0.49 (has the color red), 0.5 - 0.59 (color yellow) and 0.6 - 1 (color green).

I created a measure that calculates the score of each hospital correctly ([Score Patient Journey]) and added a table with icons that show the colors above for each hospital.

Now I need to create three KPIs, one for every group/color, that shows its share. So I need to calculate for each group the share it has within the selected hospitals. So for example I need to see which percentage of hospitals has a score equal or higher than 0.6. And if I select only one specific region in a filter, than this percentage should be recalculated and show what percentage has that group of hospitals among all hospitals of that region.

I tried this measure but it is not working. My idea was to make three different measures (one for each score group) and filter only the hospitals that have the specific score in a virtual table :

VAR table_ =

ADDCOLUMNS(FILTER('data', [Score Patient Journey]>=0.6),

"Value", [Score Patient Journey])

VAR table1 =

ADDCOLUMNS(table_,

"percentage", countx(table_, [Hospital]) / count('data'[Hospital]))

Var selectedhospital= SELECTEDVALUE('data'[Hospital])

Return minx(filter(table1, 'data'[Hospital]=selectedhospital), [percentage]

I would appreciate any kind of help. Thanks!

[Anonymous · Accepted solution]

Hi, @f_r_1998 

Thanks for @lbendlin reply. You can try the following measure. If you don't want the form to interact with other visual objects, you can use the Edit interaction function.

KPI = 
VAR _province =
    SELECTEDVALUE ( 'Table'[Province] )
VAR _countsProvince =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Province] = _province )
VAR _countsHospital =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( 'Table', [Score Patient Journey] >= 0.6 )
    )
VAR _result =
    DIVIDE ( _countsHospital, _countsProvince )
RETURN
    _result

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[f_r_1998]

Hi, thanks for getting back to me. Let me try to explain my problem better to you. Basically I have to add a KPI, but let me give you some context first:

I have a data set that includes provinces, hospitals and a score for every hospital (the score is measure I have already calculated (Score Patient Journey)). 

Province	Hospital	Score Patient Journey (measure)
A	D	0.1
A	E	0.5
A	F	0.8
B	G	0.6
C	H	1

In the dashboard I have a slicer with the Provinces and underneath a table that shows all hospitals within the selected provinces and their scores.

The hospitals can be grouped in three different groups, depending on their score. So a hospital would belong to the first group if its score is >0 and <0.5. A hospital would belong to the second group if its score is >= 0.5 and <0.6. And the third group includes hospitals that have a score > 0.6.

Now I need to add a KPI for the third group.  This KPI should show the percentage of hospitals that belong to the third group compared to all hospitals within the province selected. 

In easier words:  [Number of Hospitals belonging to the third group in province A] / [Number of all hospitals belonging to province A]

-> taking the sample data from the table above it would mean 1 / 3 = 0.333. 

Another thing that is crucial is that, when the users clicks a hospital in the table, the KPI should not change. It should solely depend and be filtered on province.

I hope explanation is better to understand. let me know if you need more info. Thank you for your help!

[lbendlin]

So a hospital would belong to the first group if its score is >0 and <0.5. A hospital would belong to the second group if its score is >= 0.5 and <0.6. And the third group includes hospitals that have a score > 0.6.

That would exclude hospital G. Please review your bucket boundaries.

[f_r_1998]

 Yes sorry,  missed an equal sign. The boundaries would be: 

So a hospital would belong to the first group if its score is >=0 and <0.5. A hospital would belong to the second group if its score is >= 0.5 and <0.6. And the third group includes hospitals that have a score >= 0.6.

So if province A is selected in the slicer, the KPI should show 1/3= 0.33, since we have one hospital belonging to the thrid group out of the three hospitals in that province. If province B is selected in the slicer, the KPI should show 1/1 = 1, since there is only one hospital in this province and it belongs to the third group.