Average help

[mahitham]

Hi Experts,

Can any one please help me on below requirement.

I have a bar chart with Month as dimension and Sum(Sales) as measure. But these sales are YTD values so for getting each month sales I have used below expression for calculation 2017 Sales.

I have calculated 2017 Sales by using below expression getting the result on this bar chart.

RangeSum(Sum({<Year={'$(=Year(Max(Date))-1)'},Month>}Sales)/1000000, -Above(Sum({<Year={'$(=Year(Max(Date))-1)'},Month>}Sales)/1000000))

Requirement:

In a Reference line I have to show avg of 2017 Sales. Please help me how to write the avg of 2017 Sales expression.

Thanks in advance

[agigliotti]

maybe this:

Avg( {< Year = {"$(=Year(Max(Date))-1)"}, Month = {"*"} >} Sales ) / 1000000

i hope it helps.

[mahitham]

Hi Andrea,

Thanks for your reply.

I am getting 0.09M in the avg reference line by the above expression.

I think I need to get 5.04 as average.

I have calculated like below in manual process

sum of all the months/12=4.87+5.66+4.73+4.96+4.15+5.94+6.24+5.71+6.07+6.38+ 5.88/12

=60.59/12=5.04

Please let me know is this correct process to validate the results.

[agigliotti]

ok so let's try the below expression:

Sum( {< Year = {"$(=Year(Max(Date))-1)"}, Month = {"*"} >} Sales ) / 12

[mahitham]

Hi Andrea,

Still not working. I am getting 35M as average.

When its Sum( {< Year = {"$(=Year(Max(Date))-1)"}, Month = {"*"} >} Sales ) / 12 then it will take ytd Values but the above chart bar values as individual month values from YTD values those calculated by the below expression.

RangeSum(Sum({<Year={'$(=Year(Max(Date))-1)'},Month>}Sales)/1000000, -Above(Sum({<Year={'$(=Year(Max(Date))-1)'},Month>}Sales)/1000000))

[agigliotti]

what about the below expression ?

=Sum(

Aggr(

(

Sum( {< Year = {"$(=Year(Max(Date))-1)"}, Month >} Sales ) / 1000000,

-

Above( Sum( {< Year = {"$(=Year(Max(Date))-1)"}, Month >} Sales ) / 1000000 )

)

, Month )

)

/12