Problems with P() and filters

[bentley32]

Hello everyone,

Let's say i do have 2 hierarchies, city and country

I am trying to create a measure which for each city will give me the avg sales for the whole country

I tried this : 

= avg({$1<Country=p()>} Sales) and it worked

But whenever i do filter with a dimension that is not in the hierarchy, per exemple type of sales, it will bring also the sales of other countries since i am asking in my formula all the possible values of each filter ...

Then i tried this avg({$1<Country, Type=p()>} Sales) and it worked again, but when i filter with the country, im getting again a wrong result with all the possible combinations of country and type ...

Do you have a trick to overcome this problem ? 

Thanks

[chrismarlow]

Hi,

I've not seen P() used like that & $1 means use the previous selection, so confused as to what you mean by working.

From what you describe I would have expected to use something like avg(TOTAL <Country> Sales), meaning discount the Country when taking the average, but it really depends on what you are using this in (you might need an aggr to average an average).

Cheers,

Chris.

[bentley32]

Hi, thanks for your help,

Doing the avg(TOTAL <Country> Sales) i can't have the sales of the country related to the city when i am filtering by a city,

It would just give the result of the city ...

Thanks

[bentley32]

Up ?

[chrismarlow]

Hi,

How about;

avg(1)*avg({<City=>}TOTAL <Country> Sales)

Cheers,

Chris.

[bentley32]

Didnt work neither ...

[chrismarlow]

Hi,

If you are still looking for a solution you will need to share more, so above suggestion was based off toy application (script below), which gives following behavior;

Now that may or may not be what you want, if it is and it does not work then would need to understand more about your data model to make alternate suggestions.

Cheers,

Chris.

data:
Load * Inline [
City, Country, Type, Sales
London, UK, 1, 10
Birmingham, UK, 1, 15
London, UK, 2, 20
Birmingham, UK, 2, 30
Paris, France, 1, 40
];