Forum Discussion
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.
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] ) )
5 Replies
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])
Hello Anonymous
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 ) )
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 sktneer MattAllington Thanks guys for the quick feedback. All 3 formulas are similar and they work perfectly.
You're welcome! Glad we could help.
