Matrix highlighting above average numbers per date

[Creative_tree88]

Hi - I have some data, which shows number of patients attending clinics.  Attached is sample (made up, of course, but based on what I have).

I have produced a matrix table which nicely shows the number of patients per clinic data, by Specialty.  See pivot chart in sample data which resembles the matrix in my Power BI app.

What I'd now like to highlight (in the matrix) is those clinic dates where the number of patients is above the average for the past 6 months - I have a data table with a 'CurrMonthOffset' field to help with this timeframe).  

So...how can I show, on my matrix chart, by way of highlighting background colour, those clinic dates where there are an above average number of patients (based on the past 6 months of patients clinic dates)?  Obviouisly my data in this sample doesn't go back 6 months but my real data covers 3 years historical and looks forward 3 months. 

I've highlighted, in yellow, the above average areas just to show you what I mean.  But this needs to be darker, the more above average the number is, like a gradient scale.

Many thanks all.

Sample Data 

[danextian]

Hi @Creative_tree88 

Create the following measures:

Average = 
AVERAGEX (
    ALLSELECTED ( 'Table'[Clinic Date] ),
    CALCULATE ( SUM ( 'Table'[PMI] ) )
)

Conditional Formatting = 
IF ( SUM ( 'Table'[PMI] ) > [Average], "Yellow" )

Then in Conditioal Formatting - Background, selected Field value and the Conditional Formatting measure

Please see the attached pbix.

[DataNinja777 · Accepted solution]

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,

[Creative_tree88]

@DataNinja777 For some reason I can't get this to work properly at all.  In your measure, there is:

'Clinic Table'[Count of PMI]

There is not a field called 'Count of PMI'.  The way I count the PMI is through a separate measure along lines of:

calculate(count('Clinic Table'[PMI]))

This gets me a count of the PMI which I think is what you're trying to do with your measure?  Apologies for any confusion - is there a way to modify your measure to build in this count measure?  Many thanks!