Competitor analysis by alternate state

[#1]

In the demo, there is only 1 Fact table, and there are 2 dimensions and 1 metric field in the table

* Agency, agency code (A - E)

* Hotel, hotel code (1 - 5)

* Booking, number of booking made by agency to hotel

first, client will select target hotel in listbox Hotel; then, select competitor hotels in listbox Hotel-CompSet;

Chart 1 shows agencies (3,4) booking to target hotel (A).

Chart 2 shows agencies (1,2,3,4,5) booking to competitor hotels (B, C, D). I defined an alternate state called CompSet and applied it to listbox Hotel-CompSet and Chart 2;

I want to show agencies (1,2,5) booking to competitor hotels (B, C, D) but not booking to target hotel (A) in Chart 3, but I'm unable to make the right expression.

Please can you advise? Thank you.

[settu_periasamy · Accepted solution]

Try this expression. in chart 3

=sum({<Hotel=P([CompSet]::Hotel),Agency=E(Agency)>} Booking)

[#3]

Great, thanks for your help, Sethu.