Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |