Wrong calculation of the average.

[yayamiko1995]

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

[Anonymous · Accepted solution]

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

[DallasBaba]

@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

[DallasBaba]

@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

[yayamiko1995_1]

@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.