Hello! I'm builing a big PowerBI report as an user interface for something that used to be a MS Access database- it's something that codes patient experience comments based on a coding matrix. The codes are made of two elements- Care Element (stuff like Quality, Staff Attitude, Medication, Booking etc.) and Care Stage (Transport/ Reception Clinical Treatment/Clinical Nursing/Discharge etc.- following a patient's journey). Each combination of Care element + care stage has a unique code.
As part of my schema, I have a table called 'Master Query' which contains all the coded comments, with columns for Code, Care Element and Care Stage. I also have a table called 'Coding Matrix' which has every unique code with its own associated Care Element, Care Stage and several other combined layers of grouping. Both tables contain columns for Code, Care Element and Care Stage, and are connected to each other by a one-to-many relationship on Code, filtering both ways.
The way the interface works: I have lots and lots of slicers for things like Service Name, Service Type, patient demographics etc.- all from different tables connected directly or indirectly to Master Query- the main function of it is that there are a bunch of visuals you can control with the slicers to generate various reports and insights.
So, what I would like to do is: create a pie chart visual that shows each care element as a % of all comments.
I can achieve this without issue by putting a measure that counts all the rows in Master Query in Values, and then the Care Element column from Coding Matrix into Legend.
The problem is: I would really like to group all slices that have less than 1% in a big slice called "Other aspects" (and then maybe create a drill-down to see all the other aspects).
How I tried doing this:
I created a calculated column to use in Legend instead of just using the Care Element column.
I even tried two different ways of calculating it:
Option 1:
Care Element Categorised =
VAR TotalComments = COUNTROWS('Master Query')
VAR CareElementCount = CALCULATE(
COUNTROWS('Master Query'),
FILTER(
'Master Query',
'Master Query'[Care Element] = 'Coding matrix'[Care Element]))
VAR CareElementPercentage = DIVIDE(CareElementCount, TotalComments, 0)
RETURN
IF(CareElementPercentage < 0.01, "Other Aspects", 'Coding matrix'[Care Element])
Option 2:
Care Element Categorised=
VAR TotalComments = COUNTROWS(ALLSELECTED('Master Query'))
VAR CareElementCount = CALCULATE(
COUNTROWS('Master Query'),
FILTER(
ALLSELECTED('Master Query'),
'Master Query'[Care Element] = 'Coding Matrix'[Care Element] ))
VAR CareElementPercentage = DIVIDE(CareElementCount, TotalComments, 0) -- Calculate percentage
RETURN
IF(CareElementPercentage < 0.01, "Other Aspects", 'Coding Matrix'[Care Element])
Both of these work perfectly fine when I just have the pie chart based on the whole data, with no slicers applied.
The problem starts (same peoblem with both) if I select a slicer- for example let's say I want to look only at Hospital Inpatients and select it from the Service Types slicer.
The categories in the pie chart without any slicer look someting like this:
- Staff attitude (23%)
- Support (18%)
- Quality (18%)
- Other aspects (23%)
- Booking (9%)
-Waiting list (7%)
- Timing (4%)
-Telephone (3%)
-Choice (2%)
-Medication (2%)
-Opening times (2%)
-Environment/layout (1%)
The categories with the slicer applied look something like this:
-Quality 25%
-Staff attitude 22%
-Other Aspects 20%
-Support 18%
-Timing 7%
-Environment/layout 4%
-Medication 2%
-Choice less than 1%
-Waiting list less than 1%
-Telephone less than 1%
-Booking less than 1%
Now, if I were to jut use the Care Element column instead of the calculated one, and apply the slicer for hospital inpatients, the categories I would get would be like:
-Quality 25%
Staff attitude 21%
-Support 18%
-Timing 7%
-Nutrition 4%
-Environment/layout 4%
- Hygiene 2%
-Equipment 2%
-Medication 2%
-Planning 2%
-Privacy/ personal property 1%
-Staffing levels 1%
-Mobility 1%
-And then several others at less than 1%.
So, the problem is, when applying the slicer, that it
- aggregates into Other Aspects several categories that have over 1% (such as Nutrition, Equipment, Planning) because they have less than 1% in total, when no slicer is applied;
- and fails to aggregate some that have under 1% (such as Booking and Opening Times) because they have over 1% in total, when no slicer is applied.
How do I make it more dynamic, so that all slices under 1% and only slices under 1% are aggregated into "Other aspects", in respect to the data selected with any slicers, not the total data? I would be very keen to get the principle of it working, because then I could do a lot of interesting and dynamic things with different layers of code groupings.
Thank you so much!
