Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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?
Hi @Luukv93,
Here is blog for your reference: Difference between DISTINCT and VALUES in DAX
Best regards,
Yuliana Gu
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |