I have healthcare data such that I have a table of visits grouped by month, caregiver, and patient. A single caregiver can have multiple patients. A single patient can have multiple caregivers. In this table, I have columns "compliant visits" and "completed visits." To calculate the % compliance, I have created a measure that takes the sum of completed visits and divides it by the sum of compliant visits.
I want a pivot table visualization such that the rows are compliance "buckets" and the columns are months that are filtered with a slicer:
| | Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 |
| 95.00%-100.00% | | | | | | |
| 90.00%-94.99% | | | | | | |
| 85.00%-89.99% | | | | | | |
| 80.00%-84.99% | | | | | | |
| 75.00%-79.99% | | | | | | |
| 50.00-74.99% | | | | | | |
| 25.00%-49.99% | | | | | | |
| 0.00%-24.99% | | | | | | |
I created this measure categorizing the Compliance% measure:
Compliance%Buckets =
IF(Compliance%]<0.25,
"0.00%-24.99%",
IF([Compliance%]<0.5,
"25.00%-49.99%",
IF([EVV %]<0.75,
"50.00%-74.99%",
IF([Compliance%]<0.8,
"75.00%-79.99%",
IF([Compliance%]<0.85,
"80.00%-84.99%",
IF([Compliance%]<0.9,
"85.00%-89.99%",
IF([Compliance%]<.95,
"90.00%-94.99%",
"95.00%-100.00%"))))))))
I need 8 of these visualization all with different values (# patients, # hours, # visits... etc). My first thought was to make summarize tables from my main table grouping on different metrics I need. However, the team I am distributing this to wants to be able to filter on caregivers who have or have not been terminated so I need to use my original table instead of grouping on patients.
The dilemma is I am getting placeholder errors in my measures because I am wanting to count specific things in my table based off the compliance% measure. This is one of the measure I want as values in my pivot table:
Patients=
CALCULATE (
DISTINCTCOUNT ( Visits[PatientID] ),
SELECTEDVALUE(ComplianceBuckets[Buckets])=[Compliance%Buckets])
Is there another way to do this?
