Matrix highlighting above average numbers per date

Hi Creative_tree88 ,

To highlight clinic dates where the number of patients is above the 6-month average in your Power BI matrix, you can use conditional formatting with a DAX measure. First, create a measure that calculates the 6-month rolling average dynamically by filtering the data to only include clinic dates within the past six months relative to the selected date. This can be done using the EDATE function to offset the date range and CALCULATE to compute the average count of patients.

Avg_Patients_6M =
VAR CurrentDate = SELECTEDVALUE('Clinic Table'[Clinic Date])
VAR Specialty = SELECTEDVALUE('Clinic Table'[Specialty])
VAR SixMonthsBack = EDATE(CurrentDate, -6)

RETURN
CALCULATE(
    AVERAGE('Clinic Table'[Count of PMI]),
    'Clinic Table'[Specialty] = Specialty,
    'Clinic Table'[Clinic Date] >= SixMonthsBack &&
    'Clinic Table'[Clinic Date] < CurrentDate
)

Next, define a measure that identifies whether the current count exceeds the computed 6-month average. This measure calculates the difference between the actual count and the average, returning a positive number if the count is above the threshold and BLANK() otherwise.

Above_Avg =
VAR CurrentValue = SELECTEDVALUE('Clinic Table'[Count of PMI])
VAR AvgValue = [Avg_Patients_6M]
RETURN
IF(CurrentValue > AvgValue, CurrentValue - AvgValue, BLANK())

After creating this measure, apply it to the matrix visual by enabling conditional formatting. Go to the format pane, expand "Cell Elements," select "Background Color," and choose the "fx" (conditional formatting) option. Use the Above_Avg measure as the rule, setting a gradient color scale where lower values appear in a lighter shade (e.g., yellow) and higher deviations from the average appear in a darker color (e.g., dark orange or red). This will result in a dynamic visual representation where clinic dates with higher-than-average patient numbers are automatically highlighted in proportion to their deviation from the average.

Best regards,