Need help with average measure

[Luukv93]

Hello,

I try to calculate the average of emails I receive per day in Power BI with the following measures, but the average (gemiddeld) is wrong.

Aantal emails = DISTINCTCOUNT(Mail[MailId])

Gemiddeld aantal e-mails per dag = 
AVERAGEX( VALUES( Datum[Date]); [Aantal emails] )

As you can see I received a total of 722 emails in 96 days so average should be 7,52 but gives me 9,28 instead.

Would really appreciate any help thank you

[v-yulgu-msft · Accepted solution]

Hi @Luukv93,

Please review the difference of results in two table visuals in below example.

Measures are:

Aantal day = DISTINCTCOUNT(Mail[Date])

Aantal emails = SUMX(DISTINCT(Mail[Date]),CALCULATE(DISTINCTCOUNT(Mail[MailId])))
Gemiddeld aantal e-mails per dag = AVERAGEX( VALUES(Mail[Date]), [Aantal emails] )

Aantal emails2 = DISTINCTCOUNT(Mail[MailId])
Gemiddeld aantal e-mails per dag2 = DISTINCTCOUNT(Mail[MailId])/DISTINCTCOUNT(Mail[Date])

In the top right table visual, the result in total row of [Aantal emails] is calculated by sum up distinctcount values of each day (3+4+2). Then, the result of Gemiddeld aantal e-mails per dag equals 9/3=3.

In the bottom right table visual, the result in total row of [Aantal emails2] is calculated by distinctcount mailid across the whole dataset. As you can see, the left table visual shows there are 4 different Email Ids. So, the result of Gemiddeld aantal e-mails per dag2 equals 4/3=1.33.

In your scenario, if you want to get the result of 7.52, you should use this measure:

Gemiddeld aantal e-mails per dag2 = 
DISTINCTCOUNT(Mail[MailId])/DISTINCTCOUNT(Mail[Date])

Best regards,

Yuliana Gu

[Luukv93]

Hi @v-yulgu-msft,

Thanks for the explanation, it makes sense.

Just one question when would you advise to use VALUES ( ) over DISTINCT () and vice versa?

[v-yulgu-msft]

Hi @Luukv93,

Here is blog for your reference: Difference between DISTINCT and VALUES in DAX

Best regards,

Yuliana Gu