Need help with a DAX function for Average on unique values

[Apsawhney]

Hi,

I have a table named Active_Users. It contains values for the count of users per location who have multiple devices in their name. Something like below - 

Location---No. of devices---Unique users

AUS             1                         50

AUS              3                          100

SG                 7                          50

I need to create a measure to calculate average users registered per location. So the formula would be something like - 

AUS = ((1*50)+(3*100))/150

SG = 7*50/50 and so on. 

Can anyone help here.

[Apsawhney]

 @jdbuchanan71 @sktneer @MattAllington Thanks guys for the quick feedback. All 3 formulas are similar and they work perfectly. 

[sktneer]

You're welcome! Glad we could help.

[sktneer · Accepted solution]

Assuming you have a table called Locations with the sample data you showed, you can create a measure like this...

=DIVIDE(
	SUMX(
		Locations,
		Locations[No. of devices]*Locations[Unique users]
	),
	SUM(
		Locations[Unique users]
	)
)

[jdbuchanan71]

Hello @Apsawhney 

I think this will get you what you are looking for, you just need to change the name of the table in the measure.

Measure = 
VAR Users = SUM ( 'Table'[Unique users] )
VAR UserDevices = SUMX ( 'Table', 'Table'[No. of devices] * 'Table'[Unique users] ) 
RETURN AVERAGEX ( VALUES ( 'Table'[Location] ), DIVIDE ( UserDevices,  Users ) )

[MattAllington]

create a table called location with all the unique locations, and join it to the active users table. 

plupace location[location] in a matrix on rows

write this measure 

Average = sumx(values(location[location]),calculate(activeUsers[devices] * activeUsers[users]))/sum(acticeUsers[unique users])