Problem with dimensions/measures and aggr()

[Edvin]

Hello guys,

Need your help, because I am frustrated.

I've got a table similar to this, of course bigger and little bit complex.

GROUP	NAME	SALES	NUMBER OF SALES	Average by GROUP	Average by NAME	Average by name/Average by GROUP
GROUP1	A	1000	4	166,66	250	-
GROUP1	B	500	5	166,66	100	-
GROUP2	C	1000	5	300	200	-
GROUP2	D	2000	10	300	100	-
GROUP2	E	3000	2	300	1500	-
GROUP3	F	800	2	600	400	-
GROUP3	G	1000	1	600	1000	-

I can easily show AVERAGE by GROUP and AVERAGE by NAME in a table chart.

But when I need to find ratio between those two, I don't get anything or I get 1 value correct but for only one NAME in GROUP.

My Average by NAME is calculated like this:

=sum({<[Year-Month]={">=$(vMinMonth)<=$(vMaxMonth)"}>} [Suma viso])/
count({<[Year-Month]={">=$(vMinMonth)<=$(vMaxMonth)"}> } [Number.])

And it's a measure.

My AVERAGE by GROUP is a dimension:

=(aggr({<[Year-Month]={">=$(vPriorMonth11)<=$(vMaxMonth)"}>} sum([Suma viso]),[GROUP])/
aggr({<[Year-Month]={">=$(vPriorMonth11)<=$(vMaxMonth)"}> } count([Number.]),[GROUP]))

So when I do like that:

=(sum({<[Year-Month]={">=$(vMinMonth)<=$(vMaxMonth)"}>} [Suma viso])/
count({<[Year-Month]={">=$(vMinMonth)<=$(vMaxMonth)"}> } [Number.]))/

(aggr({<[Year-Month]={">=$(vPriorMonth11)<=$(vMaxMonth)"}>} sum([Suma viso]),[GROUP])/
aggr({<[Year-Month]={">=$(vPriorMonth11)<=$(vMaxMonth)"}> } count([Number.]),[GROUP]))

I don't get anything. I tried to do an aggr(...) in the first expression (AVERAGE by NAME).

I would appreciate any help. Thank you very much

[lanlizgu]

Based on this data example

LOAD * INLINE [
GROUP, NAME, SALES, NUMBER_OF_SALES
GROUP1, A, 1000, 4
GROUP1, B, 500, 5
GROUP2, C, 1000, 5
GROUP2, D, 2000, 10
GROUP2, E, 3000, 2
GROUP3, F, 800, 2
GROUP3, G, 1000, 1
];

Average by Name: 

sum(SALES)
/
sum(NUMBER_OF_SALES)

Average by Group:

=sum(total <GROUP> SALES)
/
sum(total <GROUP> aggr( sum( NUMBER_OF_SALES),NAME ,GROUP))

Average by Name/ Average by Group:

(
sum(SALES)
/
sum(NUMBER_OF_SALES)
)
/
(
sum(total <GROUP> SALES)
/
sum(total <GROUP> aggr( sum( NUMBER_OF_SALES),NAME ,GROUP))
)

[Edvin]

It works perfect when I don't choose any Year-Month.

When I choose a month or a year it will bring big numbers, for ex. from 900 average to 15.000.

I try to get the ratio between 2019 December AVERAGE by NAME vs. 2019 Jan-Dec AVERAGE by GROUP.

Any suggestions?