Average of Distinccount

[Anonymous]

# DeviceUsed = Distinctcount Divice ID

week 2: 92 device is used,

Week 3: 94 device is used, 

But Week 2 and 3 : 97 device is used. 

So I am trying to do the avg device use between week 2 and 3, 

instead of doing 92+94 / 2 = 93 ; 

I got 97/2 = 48..

Can any one help me with this?

 

[v-shex-msft]

Hi @Anonymous,

It sounds like a common requirement to apply multiple aggregate functions into DAX expressions. I'd like to suggest you take a look at the following blog to apply multiple aggregations.

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , Try like

averageX(Table[week],[Avg Measure])

[Anonymous]

@amitchandak That was a super quick response. However, that's not what i need. (or am i missing something?) 

My raw table_1 looks like 

Device ID | Event Date 

Device 1       | DD/MM/YY

......

Device 99     | DD/MM/YY  

And week of the year is on a calendar table. 

So when i do distcincount of Device ID, week 2 = 92, and week 4 = 94,  how do i get averge of 94? 

according to you. I will do averagex(table_1[Event Date], and ? ). can you explain further please? 

Thank you

[mahoneypat · Accepted solution]

Please try this expression which, since you indicated you have a Calendar table, is a variation on the solution proposed by @amitchandak 

NewMeasure = AVERAGEX(VALUES('Calendar'[Week]), CALCULATE(DISTINCTCOUNT(Table_1[Device ID])))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat