Dynamic set analysis

[Anonymous]

Hi, I have a question:

I have a data set which has a City dimension and a State dimension and a measure Sales.

I'd like to create a KPI which shows the State average sales for the state to which the City in the filter pane that has been selected belongs. So for example: if "San Francisco" is selected in the City filter pane, the KPI must show the average sales amount for California, if Houston is selected, then Texas, etc.

I know it can be done with set analysis, I just don't get the exact syntax.

Kind regards and thanks in advance!

[hic · Accepted solution]

Try

Sum( {$<City=,State=P(State)>} Sales )

HIC

[Anonymous]

Thank you! It works