set analysis total by dimension

[slribeiro]

Hello.

I'm here with some issue in set analysis.

Imagine I want the following table.

ProductType	Country	Sales	Total Sales by Country
1	US	90	10000
1	China	150	15000
1	Australia	30	5000
1	Greece	100	40000
2	China	50	15000
2	Australia	20	5000
2	Greece	200	40000

To calculate Sales we use the regular sum({$<year={$(=max(year))}>} Sales), but I need help to calculate the Total Sales by Country.

The thing is to calculate something similar to sum({$<year={$(=max(year))}>} total Sales) but by country.

Thanks in advance.

[sunny_talwar]

May be this:

Sum({$<year={$(=max(year))}>} TOTAL <Country> Sales)

[slribeiro]

Yes, I've tried that and it works only if I use a straight table.

Do you have any ideia why that doesn't work in a pivot table?

best regards

[sunny_talwar]

It should still work, would you be able to share a sample where this isn't working in a pivot table?

[slribeiro]

What happens is that it only works if you have your pivot table fully expanded.

I attached a sample I've just made for you.

[sunny_talwar]

What do you want your table to display when it isn't expanded?

[Anonymous]

hi try this

aggr(rangemax(above(sum(sales)),0,rowno())),country)......for country wise sales

or

rangemax(above( TOTAL sum(sales)),0,rowno(TOTAL))) for consolidated sales...

[slribeiro]

I this first situation all the values are correct because the second column has the total of sales for each country, but if you reduce or select one TypeProduct your values will be wrong as the ideia would be to maintain the same values above

[sunny_talwar · Accepted solution]

Try this:

Sum(Aggr(Sum({1<year={$(=max(year))}>} TOTAL <countrydesc> Sales), TypeProduct, countrydesc))

or you can use this:

Sum(Aggr(Sum({<year={$(=max(year))}, TypeProduct>} TOTAL <countrydesc> Sales), TypeProduct, countrydesc))