Forum Discussion

unclejemima's avatar
unclejemima
Icon for Post Patron rankPost Patron
9 years ago

Average not working as expected

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...
Need Help
v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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. :smileyhappy:

 

Regards

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.