cancel
Showing results for
Did you mean:

## 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 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.
)
)
)```

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

## 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, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
7 REPLIES 7
Super User IV

## 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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

## 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, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

## 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.
)```
Highlighted

## 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 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
Community Support

## 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, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
Helper I

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

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 🙂

Chris

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

Hi @christoR1, 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?

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors