Average of measure based on two columns

[Anonymous]

I need to calculate the average for Rate 1 and Rate 2. Rates 1 and 2 are calculated measured based on other columns not shown. What I currently have for my average measure is: 

AverageRate1 = AVERAGEX(VALUES('Table '[NAME]), [Rate 1])

AverageRate2 = AVERAGEX(VALUES('Table '[NAME]), [Rate 2])

However, it assumes "unassigned" is one person when I need it to be two separate people because each is in a different location. How do I calculate the average of Rate 1 based on each person and location? My data is confidential, so I cannot share my pbix file. I have attached sample data. Thank you! 

[Anonymous · Accepted solution]

I figured out a way to do this. I am not sure if this is the most efficient, but it works. 

Edit Queries: 

Create calculated column 

Name 2 = If (Sheet 1 [Name]) = "Unassigned', Location, 'Sheet 1' [Name])

Then I calculated average with: 

Average 63 = Calculate (AverageX(Values('Sheet 1'[Name 2]), [Wthin 63 day]))

[v-kelly-msft]

Hi @Anonymous ，

You need 2 measures as below:

Measure = CALCULATE(AVERAGE('Table'[Rate 1]),ALLEXCEPT('Table','Table'[Location]))

Measure 2 = CALCULATE(AVERAGE('Table'[Rate 2]),ALLEXCEPT('Table','Table'[Location]))

Then you will see:

Best Regards,
Kelly

[Anonymous]

@v-kelly-msft  Thank you!

This makes sense, but Rate 1 is not a column in my table. It is a calculated measure, so I got an error when I tried your suggestion.

Currently, I have: 

AverageRate1 = AVERAGEX(VALUES('Table 1'[Name]), [Rate 1])

But then it combines Mark for both locations, when I want the measure to calculate mark as two separate people.

I tried: 

AverageRate1 = CALCULATE (AVERAGEX(VALUES('Table 1'[Name]), [Rate 1])), ALLEXCEPT ('Table 1', 'Table 1' [LOCATION]))

but that is not giving me the accurate percentage. 

Thank you! 

[TomMartens]

Hey @Anonymous ,

I guess this will work:

AVGRate1 =
AVERAGEX(
	ADDCOLUMNS(
		SUMMARIZE(
			'<tablename>'
			,'<tablename>'[Location]
			,'<tablename>'[Name]
		)
		"r1" , [Rate 1]
	)
	, [r1]
)

You can create the measure for [Rate 2] accordingly.

If this does not work, please create a pbix file that contains sample data, but still reflects your data model, upload the pbix to onedrive or dropbox and share the link.

Regards,

Tom

[Anonymous]

Hi, @TomMartens 

Thank you for your reponse! Attached is a sample pbix file. 

I created two measures to calculate the rate within 63 days and the rate within 126 days for each name. 

I need to calculate the average rate within 63 days and within 126 days. When I use the following dax, it assumes Unassigned as one person when I need it to calculate it as two. In other words, I need to calculate the average rate within 63 days by row under Name column. Thanks!! 

Sample Data