cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Apsawhney
Helper I
Helper I

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

1 ACCEPTED SOLUTION
sktneer
Resolver I
Resolver I

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

View solution in original post

5 REPLIES 5
Apsawhney
Helper I
Helper I

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

You're welcome! Glad we could help.

sktneer
Resolver I
Resolver I

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

View solution in original post

jdbuchanan71
Super User
Super User

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

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.