topic Re: Wrong calculation of the average. in DAX Commands and Tips

Wrong calculation of the average.

yayamiko1995 — Wed, 18 Oct 2023 17:28:56 GMT

Hi,

I'm dealing with an issue when i'am trying to calculate the average.

My datamodel is as follow.

For technical reasons an order may have one or more lines of command.

I' m trying to calculate the average of days before shipping of the orders.

Mathematically speaking it should be : (15 + 3+ 7+ 21)/4 = 11.5

But sadly powerbi calculate it wrongly:

I'm sure it is because of the lines of order but i don't know how to create a DAX ( i've tried but failed) measure in order to calculate it correctly.

i tried the following:

i creat a DAX measure

lol=sum(table[days before shipping)/countrows(table)

and

lol1=averagex(values(table[order number]),[lol]).

it gives me the right values but the result for each order behave badly ( the order number are different and therefore the days before shipping but it is the same issues i'm facing i just used different data):

it is actually weird because i 'm a creating a table with the order number it goes well:

thanks in advance for your help

Re: Wrong calculation of the average.

DallasBaba — Thu, 19 Oct 2023 05:25:59 GMT

yayamiko1995 the Avgerage is

ShippingDayAvg = AVERAGE(yayamiko1995[Day before shipping])

You refer to dax guide for the basic calculations and function available in dax

AVERAGE - DAX Guide

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Let me know if this work
@ me in replies, or I'll lose your thread!!!

Thanks

Re: Wrong calculation of the average.

yayamiko1995_1 — Thu, 19 Oct 2023 08:21:18 GMT

DallasBaba thanks for your answer but i don't understand what you did.

it is pretty obvious that the average of 15,3,7,21 is (15+3+7+21)/4=11,5 and not at all 8.

Re: Wrong calculation of the average.

DallasBaba — Thu, 19 Oct 2023 15:44:04 GMT

yayamiko1995_1 yayamiko1995 you right the average of 15,3,7,21 is (15+3+7+21)/4=11,5

Note: You can't ignore the repeated values when accounting for Class average, because your dataset includes duplicates of 3 & 7 ---> 15,3,3,3,7,7,21 is (15+3+3+3+7+7+21)/7=8.43 or 8

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Thanks

Re: Wrong calculation of the average.

Anonymous — Mon, 23 Oct 2023 04:03:03 GMT

HI yayamiko1995,

According to your description, it seems like a common measure total level calculation issue, you can take a look at the following blog about measure total level calculations if helps:

Measure Totals, The Final Word

Regards,

Xiaoxin Sheng