cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Need help with average measure

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Need help with average measure

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Super Contributor

## Re: Need help with average measure

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Need help with average measure

Thanks for the explanation, it makes sense.

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

Super Contributor

## Re: Need help with average measure

Hi @Luukv93,

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

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 109 members 1,528 guests
Recent signins: