cancel
Showing results for
Did you mean:
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
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]
)
)```
5 REPLIES 5
Helper I

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

Resolver I

You're welcome! Glad we could help.

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

Announcements

#### Launching new user group features

Learn how to create your own user groups today!