Please see the attached screenshot providing details of the below question.
I currently have a table at a lower dimension of States representing tickets and weeks in two different columns. For each state (row), I multiply the #of tickets by the # of weeks to get a total. I then have a sum of at the top.
My question is, how can I get the same totals at a higher level dimension (Country)?
Tickets = Sum(Tickets)
WKS = Sum(WKS)
Tickets x WKS = Sum(Tickets) x Sum(WKS)
Since in the second table I am removing the dimension of State, all that is left is one row with one summed value of tickets, and one summed value of WKS. Multiplying those two values however, produces a sum that is larger than expected output.
It sounds to me like you are looking for an average Sum(Tickets) x Sum(WKS) per some kind of dimension combination. E.g. Colour and week. To accomplish that you will need to use the aggr functionality.
AVG(AGGR(Sum(Tickets) x Sum(WKS), Color, WKS))
Plees ekskuse my Swenglish and or Norweglish spelling misstakes