LAC not working because of row fields

Question & Answer
, , ,
1

I am trying to do the sum of the averages that I have but it doesnt work because in rows I have country, station and user.
As an example:

|country|station|user|cars|
|ESP |AA |a |1 |
|ESP |AA |b |1 |
|ESP |BB |c |3 |
|ESP |BB |d |3 |
|ESP |BB |e |3 |
|ESP |CC |f |2 |
|ESP |CC |g |2 |
|ESP |CC |h |2 |

the number of cars is per station so it won’t change for the users inside of each station.

I am trying to do a matrix where I can expand the numbers by country, station and user.

I did this calculation after checking other posts but it is duplicating the results:

sumOver(
avgOver(
avg(cars)
,[{station},{user}]
)
,[{country}]
)

Here are the results I get when I diminish each row field (country,station) and what I expect:

|country|station|user|cars (incorrect)|cars (expected)|
|ESP | | |17 |6 |

|country|station|user|cars (incorrect)|cars (expected)|
|ESP |AA | |17 |1 |
|ESP |BB | |17 |3 |
|ESP |CC | |17 |2 |

If instead of using the sumover I use this formula it gives me correct numbers for station and user:
avgOver(
avg(cars)
,[{station},{user}]
)

I need a calculated field that takes into account the granularity of the Matrix based on the row fields contracted or expanded.

Doesn anyone have any idea on how to solve this?

2

Sorry if you have already tried this, but can you do

avgOver(
sumOver(
avg(cars)
,[{station},{user}]
)
,[{country}]
)

This will take the sum and then avg that sum over the country so when you close / expand it should work as you expect it to. At least that is my guess.

3