Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Need help with a DAX function for Average on unique values

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---Uniq...
Need Help
  • sktneer's avatar
    sktneer
    7 years ago

    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]
	)
)
MattAllington's avatar
MattAllington
Community Champion
7 years ago

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])