How to Sum then Average based on certain conditions in a DAX Measure?

[rush]

I have a table called Billing Info which has columns:

• Year-Month (Text)
• UserName (Text)
• GP (Decimal Number)
• BillingType (Text) - which needs to be filtered down to "Fixed Monthly Rate"

I would like to work out the Average GP for Fixed Monthly Rate per Year-Month.

I would need to sum the total GP per UserName per Year-Month then work out the average over that.

Sample Data

Example:

[brickanalyst]

@rush it would be nice if you share how you managed in the end?

[v-qiuyu-msft]

Hi @rush,

Measure1 = IF(MAX('Table1'[BillingType])="Fixed Monthly Rate", CALCULATE(AVERAGE(Table1[GP]),FILTER(ALL('Table1'),'Table1'[BillingType]="Fixed Monthly Rate" )),BLANK())

Measure 2 = CALCULATE(AVERAGE(Table1[GP]),ALLEXCEPT(Table1,'Table1'[UserName]))

If above DAX doesn't meet your requirement, please share screenshots about the desired results for our analysis.

Best Regards,
Qiuyun Yu

[rush]

Thank you @v-qiuyu-msft

It does not bring back my desired results.

Please see below of the final result:

Table desired results:

[v-qiuyu-msft]

Hi @rush,

As your sample data you provided doesn't contain StaffRegion while the desired results have it, you can try the below DAX:

Measure = CALCULATE(AVERAGE(Table1[GP]),FILTER(ALL(Table1),'Table1'[Year-Month]=MAX('Table1'[Year-Month])),VALUES(Table1[StaffRegion]))

If it doesn't work, please share corresponding sample data. If possible, please share the .pbix with us.

Best Regards,
Qiuyun Yu

[rush]

Hi @v-qiuyu-msft, thanks but it gives me an error on the text to type number.

I will update the sample file as I cannot share the pbix file.

It throws back an error:

[v-qiuyu-msft · Accepted solution]

Hi @rush,

From the error message, column [Gauteng] is TEXT data type. And you have specify the aggregation (eg: SUM) for this column in the measure while this aggregation doesn't support TEXT values. So please check the [Gauteng], see if it can be convert to number.

By the way, you can upload your .pbix file to OneDrive or Dropbox and share the link here.

Best Regards,
Qiuyun Yu 

[rush]

@v-qiuyu-msft Thanks, I managed to resolve the issue.

[anupampandey]

@rush,

Could you share some sample data please?

Thanks,

Anupam