How to change the granularity of an average over a category

[SQLguy]

Hi all,

I need some help with creating a measure that will calculate an average over a category. I'm creating a report for a hospital to find out whether our depression patients are improving. To do that, we use a standardized depression screening questionnaire (the PHQ-9) and see if the score increases (more depressed) or decreases (less depressed) over time. I want to compare discharge departments to find out which ones' patients are showing the most improvement on average. I also want to exclude patients who only had 1 screening. We can't measure change without multiple scores, and we don't want those patients to water down the average.

Here's some sample data: (NOTE: this data is purely fictional and does not represent information about real people)

Patient ID	Patient Name	PHQ-9 Score	Discharge Department	PHQ-9 Recorded Time
1	ONE, PATIENT	25	Sample Clinic	1/1/2015
2	TWO, PATIENT	26	Sample Clinic	1/2/2016
2	TWO, PATIENT	7	Sample Clinic	1/3/2017
2	TWO, PATIENT	8	Sample Clinic	1/4/2018
3	THREE, PATIENT	20	Sample Clinic	1/5/2012
3	THREE, PATIENT	25	Sample Clinic	1/6/2014
3	THREE, PATIENT	7	Sample Clinic	1/7/2017
4	FOUR, PATIENT	20	Sample Clinic	1/8/2018
4	FOUR, PATIENT	23	Sample Clinic	1/9/2015
5	FIVE, PATIENT	26	Sample Clinic	1/10/2016
5	FIVE, PATIENT	7	Sample Clinic	1/11/2018

I made a matrix that shows some measures for each patient and for each department. The extracted data is below:

Department/Patient	PHQ-9 Count	Average PHQ-9 Score	First PHQ-9 Value	Last PHQ-9 Value	PHQ-9 Score Overall Change	Average PHQ-9 Change per Disch Dept
Sample Clinic	11	18.82	20	7	-13	-13
ONE, PATIENT	1	25	25	25
TWO, PATIENT	3	13.7	26	8	-18	-18
THREE, PATIENT	3	17.4	20	7	-13	-13
FOUR, PATIENT	2	21.5	20	23	3	3
FIVE, PATIENT	2	16.5	26	7	-19	-19

Everything looks correct except the Average PHQ-9 Change per Discharge Department. It shows -13 for Sample Clinic, but I think it should should be -11.75 (-18 + -13 + 3 + -19 = -47 ÷ 4 = -11.75.) It looks like PBI is calculating the average change for the department the same way a single patient's change is calculated, i.e. the difference between the last and first recorded PHQ-9 values: 8 - 21 = -13 ÷ 1 = -13. How do I rewrite the measure to show the value I'm looking for?

Here's my DAX:

First PHQ-9 Value = SUMX( 
    FILTER(
        'PHQ-9 All',
        'PHQ-9 All'[PHQ-9 Recorded Time] = MIN( 'PHQ-9 All'[PHQ-9 Recorded Time] )
    ),
    'PHQ-9 All'[PHQ-9 Score]
)

Last PHQ-9 Value = SUMX( 
    FILTER(
        'PHQ-9 All',
        'PHQ-9 All'[PHQ-9 Recorded Time] = MAX( 'PHQ-9 All'[PHQ-9 Recorded Time] ) 
    ),
    'PHQ-9 All'[PHQ-9 Score]
)

PHQ-9 Score Overall Change = CALCULATE(
    [Last PHQ-9 Value] - [First PHQ-9 Value],
    FILTER( 
        'PHQ-9 All', 
        COUNTA( 'PHQ-9 All'[Patient ID] ) > 1 //Limit the query to patients who had multiple PHQ-9 scores.
    )
)

Average PHQ-9 Change per Disch Dept = AVERAGEX(
    VALUES('PHQ-9 All'[Discharge Department]),  //For each discharge department
    CALCULATE( [PHQ-9 Score Overall Change],    //Find the difference between the last and first PHQ-9 scores for each patient.
        FILTER( 'PHQ-9 All', 
            COUNTA( 'PHQ-9 All'[Patient ID] ) > 1 //Limit the query to patients who had multiple PHQ-9 scores.
        )
    )
)

Thank you in advance!

[Anonymous]

Hi @SQLguy , May i ask how did you calculate the Average PHQ-9 Score? i have a similar situation where there are multiple case ID's & score and i want to show a distinct on list of ID's with an Average score. Your Average PHQ-9 Score calc should work for me 🙂

Thanks in advance

Chris

[SQLguy]

Hi @Anonymous, the measure calculation is simply 

Average PHQ-9 Score = AVERAGE( 'PHQ-9 All'[PHQ-9 Score] )

The granularity of the table is one row per office visit, so there may be more than one row for each patient ID. The implicit CALCULATE() in the measure handles the context transition. For example, if I create a table that has unique patient IDs in one column and [Average PHQ-9 Score] in another, the measure will calculate for each row the average of the [PHQ-9 Score] column for that patient's visits only. Does that answer your question?

[v-shex-msft · Accepted solution]

Hi @SQLguy,

AFAIK, if you measure contains complex filters or conditions based on current row contents, it probably not works on total level.

I'd like to suggest you take a look at following links about how to handle measure formula calculate on hierarchy total level:

Clever Hierarchy Handling in DAX

Measure Totals, The Final Word

I think you need to add conditions to check current row contents to ensure which hierarchy level is, then write measure formulas for correspond total level calculation.

Regards,

Xiaoxin Sheng

[SQLguy]

@v-shex-msft

Thank you very much for your helpful links! I followed the instructions in the link "Measure Totals, The Final Word", and that worked! I want to make sure that I understand why it worked, though. Can you tell me if this is correct?

Problem: I was trying to do two different calculations in the same column, one for each level in the hierarchy.

Solution: Calculate the measure in two steps.

1. Define a table variable that summarizes the PHQ-9 score changes.
2. Calculate the average over that table.

Avg PHQ-9 Change = 

//Create a new summary table using the PHQ-9 scores fact table. Group by department
//then by patient. Subtract the last score from the first score for each patient.
//Include only patients with more than 1 score.
VAR _table = 
	SUMMARIZE(
		'PHQ-9 All',
		'PHQ-9 All'[Discharge Department],
		'PHQ-9 All'[Patient Name],
		"PHQ-9 Score Change", [PHQ-9 Score Overall Change]
	)
RETURN

//Only show the average for the department level.
IF(
    HASONEVALUE( 'PHQ-9 All'[Patient Name] ),
    BLANK(),
    AVERAGEX(
        _table,
        [PHQ-9 Score Change]
    )
)

The result of '_table' looks like this:

Discharge Department	Patient Name	PHQ-9 Score Change
Sample Clinic	ONE, PATIENT
Sample Clinic	TWO, PATIENT	-18
Sample Clinic	THREE, PATIENT	-13
Sample Clinic	FOUR, PATIENT	3
Sample Clinic	FIVE, PATIENT	-19

The new matrix looks like this:

Department / Patient	PHQ-9 Count	Average PHQ-9 Score	First PHQ-9 Value	Last PHQ-9 Value	PHQ-9 Score Overall Change	Avg PHQ-9 Change
Sample Clinic	11	18.82	20	7	-13	-11.75
ONE, PATIENT	1	25	25	25
TWO, PATIENT	3	13.7	26	8	-18
THREE, PATIENT	3	17.4	20	7	-13
FOUR, PATIENT	2	21.5	20	23	3
FIVE, PATIENT	2	16.5	26	7	-19

[v-shex-msft]

Hi @SQLguy,

Actually, measure formula not directly calculated on summarize total level which displayed,  it will drill to detail records and calculate on them instead of direct calculated on summarize result. 

In addition, dax functions which you used to get current row contents will also affected by different hierarchy level.

For instance:
Single row level: max, min... functions are calculated on one row, so they can used to get current row.

Total level(row contents contains multiple records): above methods will calculate on current rows contents and get summarize result instead get list of values.

When you create variable table with summarize result, row content has been limited on single row level(for each calculation) and fixed as a static column value.

So you can get correct summarize result based on static single row measure calculation result.

Hope above helps.

Regards,

Xiaoxin Sheng

[Ashish_Mathur]

Hi,

What is the logic for -18 + -13 + 3 + -19.  Why are you excluding the other numebrs from the "PHQ-9 Score Overall Change" column?  Please explain.

[SQLguy]

@Ashish_Mathur

The calculation should exclude patients who only had 1 PHQ-9 screening. Patient One had only one screening, so we cannot know whether his depression got better or worse. If we include those patients in the average, they will pull the average down.

I changed my DAX formula to use the FILTER logic below. Is this the correct way to filter the query to only patients who had more than one screening? Please let me know if there is a better one.

FILTER( 'PHQ-9 All', 
            COUNTA( 'PHQ-9 All'[Patient ID] ) > 1 //Limit the query to patients who had multiple PHQ-9 scores.
        )