Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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 @sktneer @MattAllington Thanks guys for the quick feedback. All 3 formulas are similar and they work perfectly.
You're welcome! Glad we could help.
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] ) )
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])
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
99 | |
97 | |
73 | |
72 |