Average count from a column using dax

[Anonymous]

Hi,

I have two columns.

File_id        Attachment_id

1                  2123

1                  3423

1                  3423

2                  453

3                  12

4                   32

4                  2312

5                  232   

6                  3123

Now I need to calculate average number of attachments added per file_id. In above case file_id=1 has 3 attachment, file_id=2 has 1 attachement and so on. Now I need an average of number of attachments per file_id using DAX. Please help!

[dtartaglia]

Hi,

Are you looking for 1 value for average or 6; one per id? What is your visual or output look like?

[Anonymous]

@dtartaglia: I am looking for one value i.e Average number of attachments per file. 

[Thiyagu]

Hi @Anonymous

Hope below answer works for your requirement.

 Measure = CALCULATE(AVERAGE(comm[Attachment_Id]),FILTER(ALL(comm),comm[File_ID]=MAX(comm[File_ID])))

- Thiyagu

[Anonymous]

@Thiyagu It seems you have taken average of 'sum' of attachment_id. I just require 'averager count of attachment_id' per file_id

For instance

File_id      Attachment_id

1                 4

1                 3

1                 34

2                 323 

2                 33

3                 33

3                 333

4                 23

Now in this case outcome should be: 8 ( unique attachment_id) / 4 (unique file ids) = 2 i.e on an average people add 2 attachments to a file.

[v-yuezhe-msft · Accepted solution]

---

@Anonymous wrote:

@Thiyagu It seems you have taken average of 'sum' of attachment_id. I just require 'averager count of attachment_id' per file_id

For instance

File_id      Attachment_id

1                 4

1                 3

1                 34

2                 323 

2                 33

3                 33

3                 333

4                 23

Now in this case outcome should be: 8 ( unique attachment_id) / 4 (unique file ids) = 2 i.e on an average people add 2 attachments to a file.

---

@Anonymous,

Based on the above sample data, you would need to create a measure using the DAX below.

Measure = DIVIDE(COUNT(Table[Attachment_id]),DISTINCTCOUNT(Table[File_id]))

Regards,

[Thiyagu]

Hi,

It seems below is ur requirement,

=DIVIDE(DISTINCTCOUNT(comm[Attachment_Id]),DISTINCTCOUNT(comm[File_ID]))

Try this measure