Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
)
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?