Average not working as expected

[unclejemima]

I've got a line graph showing Current Year Sales from a measure called "TotalSales".

If I use the Average Line function, the results are correct showing the average of the available data (Jan, Feb, Mar & April)

If take TotalSales measure and use Average DAX funtion, the number it returns is lower.

I've tried making TotalSales a Column instead of measure and then changing value to "Show value as average", and it still returns the wrong value.

Any idea's?

[unclejemima]

I put the average calc into a table to view the results...

Correct Actual : Jan 283, Feb 300, Mar 321, Apr 106. = 1010/4 Months = 252.5

Using average measure is averaging sales in each month (I assume) and the final number is wrong.  Table view shows.

Jan 249, Feb 261, Mar 215, Apr 165 = 229.  This is flat our wrong.  I'm not sure whats going on or where its getting the numbers from.

I just want to take the monthly sales (actual) divide by number of months and that will give the correct number

[unclejemima]

Well getting close...I used 

AveragedSales = calculate(ViewInvoice[TotalSales]/MONTH([Today]))

The number is correct now...but I'm still thinking this is not the best way to do this?

[v-ljerr-msft]

Hi @unclejemima

Have you tried the solution provided above? Does it work in your scenario?

If you still have any question on this issue, feel free to post here. 

Regards

[Anonymous]

Hello i will elaborate the situation i am facing currently.

I want to calculate the average of value and volume, and my data has the following hierarchy Category>Brands>Packgroup>PPG>SKU. So what i mean is if you add total brand you get category, similary you add total packgroup you get category and the same applies for PPPG and SKU. Now i need a view in which i need 03 matrix - Matrix 1 at category level, Matrix 2 at Brand level and Matrix 3 at Packgroup level.

Additionally i have 36 months data, and 20 Brands, 6 packgroups, also have included a hierarchy tag in the excel sheet to filter data based on hierarchy, now my goal is to when i click on any brand the Category matrix and Packgroup show me the result based on the selection, now whats happening with this case if :-

1) The sum funcition is show accurate result -

ValY-SUM = CALCULATE(SUM(STACKED[Value]), DATESBETWEEN(STACKED[Calendar], STARTOFYEAR(STACKED[Calendar]),ENDOFYEAR(STACKED[Calendar])))

2) The average funciton is however showing inaccurate, actually its not applying the filter properly - 

ValY-AVG = CALCULATE(AVERAGE(STACKED[Value]), DATESBETWEEN(STACKED[Calendar], STARTOFYEAR(STACKED[Calendar]),ENDOFYEAR(STACKED[Calendar])))

Please help me to resolve it.

[v-ljerr-msft]

Hi @unclejemima,

As you haven't post your table structure, I just assume you have a table called "ViewInvoice" with a "Date" column and a "Revenue" column.

So your [TotalSales] measure should be like below.

TotalSales = SUM(ViewInvoice[Revenue])

Then should be able to simply use the formula below to calculate the average sales. 

AveragedSales = AVERAGE(ViewInvoice[Revenue])

Regards