Sum as a % of dimension

[mariovella]

Can anyone kindly help me?

I have two dimensions (Region, city) and a measure (sale). Here the values

Region	City	Sales
Lombardy	Milan	127.391,33
Lombardy	Milan	119.348,40
Lazio	Roma	18.511,46
Tuscany	Florence	87.786,00
Tuscany	Siena	55.312,22
Tuscany	Florence	200.691,51
Lombardy	Pavia	68.262,00

How can I manage the set analysis in order to have the sales in Milan as a percentage of the sales in Lombardy ?

The result is (127.391,33+119.348,40)/(127.391,33+119.348,40+68.262,00).... but I don't know what to do in  a set analysis.

Thanks for any help

Mario

[sunny_talwar · Accepted solution]

May be this

Sum(Sales)/Sum(TOTAL <Region> Sales)

[campbellr]

Hi Mario

The set analysis to get sales for milan as a percentage of sales lombardy:

Sum({<City={'Milan'}>} Sales) / Sum({<Region={'Lombardy'}>} Sales)

Ron

if you want it to be based on selection then it can get a little trickier

[mariovella]

Ron,

thanks a lot for your help.

As you said, I'm actually looking for a formula  based on selection.

Can you help me ?

THANKS

[sunny_talwar]

I'm actually looking for a formula  based on selection.

Can you elaborate a little on this?

[mariovella]

Great!! Thanks a lot Sunny. A great help for me

[campbellr]

you can try this, similar to what Sunny suggested.

Sum(total <city> Sales) / Sum(total <Region> Sales)