AVG per dimension per time using set analysis

[nat99]

Hi, 

I have a pivot table looks like this: 

Product	Jan		Feb
	SUM(Amount)	Average Amt Per Product Per Month	SUM(Amount)	Average Amt Per Product Per Month
A	10		15
B	20		25
C	30		35

So I would like to calculate Average Amt Per Product Per Month. Currently I use this but it didn't work (return 0)

SUM(Amount)/COUNT({1<[Month] = {"$TOP([Month])"}>}DISTINCT total [Product] & '|' & [Month])

I also tried to see if TOP([Month]) returns correct value and yes, it matches its corresponding month for each of the row there, but when I include it to the whole calculation then it doesn't work. 

Basically my idea is to have the count calculation ignoring the Product field and getting dynamic with the Month field. 

Please advise.

Thank you so much 

[stevedark]

Hi @nat99 

I'm not quite sure what figure you are attempting to get to here.

If it is just the average number of those that are in the chart, then you would just use avg(Amount).

If the 10 for product A in January was made up of two 2s and a 6 this would give you 3.33 as an average ((2+2+6)/3).

If it is not that you are after can you please explain what values you want to have calculated.

With the set analysis you have [Month] = {"$TOP([Month])"} will not work, as it will not calculate what is in the quotes (it will try and match to $TOP([Month]) as a literal string.

The following may work:

[Month] = {"$(=TOP([Month]))"}

But, you are more likely to need to put formatting in to match the formatting of the Month field, e.g.:

[Month] = {"$(=Date(TOP([Month]), 'MMM-YYYY'))"}

I don't think that this is the only issue though.

If you let me know what you are aiming for I will be able to help further.

Cheers,

Steve