cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SQLguy Frequent Visitor
Frequent Visitor

How to change the granularity of an average over a category

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 IDPatient NamePHQ-9 ScoreDischarge DepartmentPHQ-9 Recorded Time
1ONE, PATIENT25Sample Clinic1/1/2015
2TWO, PATIENT26Sample Clinic1/2/2016
2TWO, PATIENT7Sample Clinic1/3/2017
2TWO, PATIENT8Sample Clinic1/4/2018
3THREE, PATIENT20Sample Clinic1/5/2012
3THREE, PATIENT25Sample Clinic1/6/2014
3THREE, PATIENT7Sample Clinic1/7/2017
4FOUR, PATIENT20Sample Clinic1/8/2018
4FOUR, PATIENT23Sample Clinic1/9/2015
5FIVE, PATIENT26Sample Clinic1/10/2016
5FIVE, PATIENT7Sample Clinic1/11/2018

 

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

 

Department/PatientPHQ-9 CountAverage PHQ-9 ScoreFirst PHQ-9 ValueLast PHQ-9 ValuePHQ-9 Score Overall ChangeAverage PHQ-9 Change per Disch Dept
Sample Clinic1118.82

20

7-13-13
ONE, PATIENT1252525  
TWO, PATIENT313.7268-18-18
THREE, PATIENT317.4207-13-13
FOUR, PATIENT221.5202333
FIVE, PATIENT216.5267-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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: How to change the granularity of an average over a category

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
5 REPLIES 5
Highlighted
Super User
Super User

Re: How to change the granularity of an average over a category

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.

Community Support Team
Community Support Team

Re: How to change the granularity of an average over a category

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
SQLguy Frequent Visitor
Frequent Visitor

Re: How to change the granularity of an average over a category

@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.
        )
SQLguy Frequent Visitor
Frequent Visitor

Re: How to change the granularity of an average over a category

@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 DepartmentPatient Name                  PHQ-9 Score Change
Sample ClinicONE, PATIENT 
Sample ClinicTWO, PATIENT-18
Sample ClinicTHREE, PATIENT-13
Sample ClinicFOUR, PATIENT3
Sample ClinicFIVE, PATIENT-19

 

The new matrix looks like this:

Department / PatientPHQ-9 CountAverage PHQ-9 ScoreFirst PHQ-9 ValueLast PHQ-9 ValuePHQ-9 Score Overall ChangeAvg PHQ-9 Change
Sample Clinic1118.82207-13-11.75 Smiley Happy
ONE, PATIENT1252525  
TWO, PATIENT313.7268-18 
THREE, PATIENT317.4207-13 
FOUR, PATIENT221.520233 
FIVE, PATIENT216.5267-19 
Community Support Team
Community Support Team

Re: How to change the granularity of an average over a category

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |